Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Just Deleting data will reclaim space?? Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 9:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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
Post #1407945
Posted Wednesday, January 16, 2013 9:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:51 AM
Points: 248, Visits: 793
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).

Post #1407946
Posted Wednesday, January 16, 2013 9:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:51 AM
Points: 248, Visits: 793
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.
Post #1407949
Posted Thursday, January 17, 2013 8:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:25 AM
Points: 1,229, Visits: 2,727
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.



Post #1408466
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse