question

ruancra avatar image
ruancra asked

Reclaim space after deleting large amounts of data - SQL 2008

Hi all We are busy with a archive process whereby we are deleting millions of records from our transaction tables, and moving the data to archive tables with triggers. The Archive tables are located on a separate filegroup, which exist on a different drive. We have so far successfully been deleting records from the transaction tables, but i dont see the space of the tables or database decreasing. My question is, will SQL automatically reclaim the free space available or do i need to run a command? Thanks Ruan
delete
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
No, SQL Server will not give back the space to the OS without you requesting it to. The theory is that acquiring that space is a very expensive operation and would slow down data transaction processing when it occurs so once taken it is kept in readiness for it being needed in the future. This is the reason why you should have space in all your databases so that transactions are not constantly waiting for disk space to be acquired. It sounds like you have a lot of space though. If you know for sure that you wont need this space for a long time (years) then is can be released to the OS if you want to/need to. Review the syntax for DBCC SHRINKFILE http://technet.microsoft.com/en-us/library/ms189493.aspx and DBCC SHRINKDATABASE http://technet.microsoft.com/en-us/library/ms190488.aspx in book online
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image ruancra commented ·
Thanks Allen I thought about running a DBCC SHRINKDATABASE command, but it takes too long to complete. Will have to schedule downtime to do it as it is a production database. There is 17Gb left on the hard drive. The Transaction tables are filling up quite quickly.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
If they are filling up again then it would suggest that shrinking the database would be a waste of time as it will then need to grow in a short space of time
0 Likes 0 ·
ruancra avatar image ruancra commented ·
We are archiving 20 000000 records out of 27 000000. Not sure how quickly it will fill up again. There are 5 core Transaction tables, the database is over 200Gb. Im sure a SHRINKDATABASE will optimize the performance of database. After the archiving is done, we also want to REINDEX the tables as the existing indexes are heavily fragmented.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
The empty space will not slow down performance but having to grow the file again over time will. Index fragmentation needs to be addressed, it will certainly be slowing processing if it is high. This is more important than the free space. The only thing making the free space important is if the server needs the space for other processes outside SQL Server
0 Likes 0 ·
ruancra avatar image ruancra commented ·
The SHRINKDTABASE doesn't seem necessary then. We will however rebuild the indexes when the archiving is complete. Thanks for the advice PappaJack!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.