Our server admin is asking me to free up disk space on our SQL server, since the drive where the SQL datafiles reside is at 97% usage.
I have found a table that has grown out of control. It now contains over 300 million rows, so I will set a job to delete these overnight.
- What are my options to then free up the disk space?
- Will the deletion of those rows free up space for other tables to use?
I would like to avoid SHRINKFILE
as it is not recommended.
Best Answer
First, I would confirm what the server admin is seeing, by running some free-space diagnostics on your SQL Server:
Then check which files are the biggest/worst on the problem drive:
Before you go the SHRINKFILE route, you should ask yourself some higher level questions, like:
SHRINKFILE
right now?Since you have found a table with data that can (hopefully) be deleted (check with the people who use that data!), go ahead and delete the data, possibly in batches if you are worried about locking the table for a long time:
If there are useful rows left in your table after getting rid of the old/useless ones, I would rebuild the table's Primary Key:
...and finally shrink the database file. I would try using the TRUNCATEONLY option first, as it's faster/safer than a normal SHRINKFILE which rearranges pages within the DB file: