May 21, 2009 at 6:02 am
Hi,
I want to delete all data from some tables, but i don't want to drop does tables.
If i delete the data, will the information on the indexes of that tables be realsed?
Suppose that i have a table (size 200MB of data) , this table have two indexes.
If i delete the data on the table (delete from table_name), the table will be empty. What
about the index size? will it still be the same?
tks,
Pedro
May 21, 2009 at 7:20 am
Yes the indexes will be updated as you delete the data. The way SQL server stores data in 8K pages may mean that what remains of the index is larger than simply dropping the index and re-creating it after you've emptied the table, but most of the pages allocated to the index will be freed
If you want to remove all the data, use TRUNCATE rather than DELETE FROM, as this will be minimally logged.
If your objective is to free up disk space - you may not notice any difference until after you do a 'shrink'.
May 21, 2009 at 7:36 am
Ok, thank you very much for the help.
About the truncate.
If i have the recovery model in Simple mode, do i get any think by making a truncate instead of a delete?
thanks,
Pedro
May 21, 2009 at 7:46 am
pedro.ribeiro (5/21/2009)
Ok, thank you very much for the help.About the truncate.
If i have the recovery model in Simple mode, do i get any think by making a truncate instead of a delete?
thanks,
Pedro
I think that TRUNCATE not depends from recovery model even it is Simple or Full! If you planing to delete all data sure that you would use TRUNCATE, the data will be deleted much faster than DELETE command!
But, on the other hand check the constraints if your table is related to any other table, otherwise the TRUNCATE command will not work properly!
May 21, 2009 at 9:14 am
yap, the truncate in this case will not work.
What about the shrink?
I need that after the data is deleted , to shrink the database.
But to what size?
May 21, 2009 at 9:22 am
In theory Simple recovery model means the transaction log should be truncated regularly - this doesn't always mean the disk space is freed up.
Try shrinking to the minimum size suggested in enterprise manager. I've found it rarely shrinks that small anyway.
May 22, 2009 at 11:34 am
That is a great ideia, but i have a problem.
I can not see what enterprise manager will sugest, because this is a script that will be sent to some remote locations.
(the script will delete more that alf of the data inside the databases)
What i need to know is to wich size can i truncate the database to free disk space and for the future backups to be smaller.
do you have any other ideias?
thank you,
Pedro
May 22, 2009 at 2:51 pm
In reality, if you delete the data and don't shrink your files (and there are good reason why you shouldn't), your backup file will be smaller. It doesn't backup free space. Perhaps in your script, run something like sp_spaceused to a table and read the table to get the new size as a target??
A couple of caveats, if you shrink the data file, you will need to rebuild your indexes because the shrink will destroy their usefullness. In running the rebuild index, your data file and transaction file will grow (even in simple recovery, your log will grow some). Unless you are freeing up disk space because you have exhausted the current available space and cannot add more, I wouldn't even consider shrinking - IMO.
Search this site to find a myriad of reasons not to shrink your files and the few where it is somewhat acceptable.
-- You can't be late until you show up.
May 31, 2009 at 9:02 am
ok. I undestud.
But, why do i need do rebuid indexes if i shrink a database?
June 1, 2009 at 5:22 am
Refer to
"DBCC SHRINKFILE"
in books online. This is how you'd script it in T-SQL, however I'd read up on it first to see if its really what you need.
In the SQL 2005 version of BOL (I don;t have access to the SQL 2000 version at the moment)
It actually states that a shrink operation is most effective afer an operation that creates lots of unused space such as a truncate table operation.
However it advises against repeatedly shrinking a database because of fragmented indexes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply