January 15, 2013 at 9:42 pm
Hi Experts,
We have deleted 144101324 records from our WH database will it release the space to disk?
Thanks in Advance.
January 15, 2013 at 9:56 pm
Ratheesh.K.Nair (1/15/2013)
Hi Experts,We have deleted 144101324 records from our WH database will it release the space to disk?
Thanks in Advance.
Do you mean to the database or the OS? If the former, yes; if the latter, no. To release space back to the OS you would have to shrink the database, and unless the space is critical and needed for other resources I wouldn't shrink the data.
January 15, 2013 at 10:06 pm
Thanks for quick response Lynn.
I mean to OS..
January 15, 2013 at 10:12 pm
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.
January 15, 2013 at 10:15 pm
Not knowing the data that is being delete or the system itself, I couldn't tell you. I would recommend rebuilding the the clustered and non-clustered indexes on the tables if you are deleting large number of rows for specific tables.
January 16, 2013 at 4:11 am
DELETE WITH (TABLOCK) will immediatly release space to database. If you want it to OS, then you need to shrink the database/file.
January 16, 2013 at 4:24 am
Ratheesh.K.Nair (1/15/2013)
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.
If the deletes are batched up you won't have log growth problems but if the delete is one large transaction you could still get log growth problems
---------------------------------------------------------------------
January 16, 2013 at 5:59 am
Thanks SQL Show & George
January 16, 2013 at 6:00 am
george sibbald (1/16/2013)
Ratheesh.K.Nair (1/15/2013)
Dev team have created queries to remove duplicate records from DB and it is removing this many records per year starting from 2009. Is it good or will it cause any overhead. Database is in SIMPLE recovery mode so log growth is not am concerned about.If the deletes are batched up you won't have log growth problems but if the delete is one large transaction you could still get log growth problems
The query is deleting duplicate records its fetching data using cursor.
January 16, 2013 at 8:08 am
if its deleting one at a time dont expect problems with the log but it will run slowly.
---------------------------------------------------------------------
January 16, 2013 at 9:44 am
Do a DBCC SHRINKFILE or from SSMS -> right click the Database -> Tasks -> Shrink - >File (check for available free space - if this is 90% or higher you can definitely shrink the file if in simple recivery mode)and click ok. OR you can specify a shrink file size in the shrink action, you can also get the TSQL for shrink operation..if you click on the SCRIPT Drop Down on TOP of the window.
Maninder
www.dbanation.com
January 16, 2013 at 9:46 am
If the table is a HEAP only DELETE WITH (TABLOCK) or truncate are guaranteed to release space.
Best way to delete from a large table is to traverse the clustered index via a loop and delete in
chunks (a few k rows at a time).
January 16, 2013 at 9:48 am
If you are going to shrink the data file(s), you should leave enough space to rebuild the clustered indexes of your largest table.
After shrink you must rebuild all clustered indexes.
January 17, 2013 at 8:37 am
Deleting data will cause the backup size to decrease but the file sizes of the .mdf files will not. However, you will fragment the data within the tables you delete from so rebuilding the indexes will make the total used within the data file get smaller as the clustering index will clean up all of the holes you created by deleting data. HOwever, the file size of the .mdf won't get smaller. Like others said you will have to use the SHRINK command to do this. However, do NOT run this on a heavily used db, run it during a quiet time as it could potentially lock people out. Also keep in mind if you shrink the .mdf you are now fragmenting the data within the database so you should probably rebuild the indexes afterwards.
A lot of folks here would say do NOT shrink your files because you cause more fragmentation within the file and it will grow again as you add more data so you aren't gaining anything. About the only reason to shrink a file, some would say, is if your drive is dangerously about to run out of space.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply