I have a server that the database drive is running out of space. i have deleted some of the old log data and historical data to try and free up the space.what is the best way to free allocated space in SQL server. the only way i could find online is to shrink the database but then i read shrinking the database can cause problems I have tried using shrink database and it works but i'm just concerned if i'm using the correct method


The problems with shrinking database files are primarily:

  1. The shrinking process can cause fragmentation in tables and indexes.
  2. Barring unusual circumstances, the files will need to be grown again in the immediate future.

Both of these can cause performance issues. Fragmentation of your tables and indexes can make queries take longer, and growing your log file (or your data file, if you don't have instant file instantiation turned on) takes a certain amount of time, where users may be waiting for it to complete.

"Unusual circumstances" would mean some change was made to the database such that space in the data file is not longer needed (for example, the permanent removal of a large table, or the deletion of millions of rows that won't be replaced shortly), or an abnormally large amount of space was required for the log file (for instance, a once-a-year data load cause your log file to grow from its norm 1 GB size to 35 GB; or, your database was using the FULL recovery model but you weren't taking transaction log backups and you've now resolved this (either by starting the backups, or by switching to the SIMPLE recovery model)).

In those unusual circumstances, shrinking your data or log files to a more reasonable size makes sense.

Note: Having a nightly job that shrinks these files doesn't make sense. If you're shrinking either file that often, then it must be growing larger than your job's target daily. If that's happening, you need to rethink that target, and think about adding space to your drives.

A further note: If you have heap tables (tables with no clustered index), SQL Server isn't good about releasing space once columns in those tables are no longer in use. Solution to this include copying the data to a new table, or adding a clustered index.

You need to asses your own system, and determine why you're running low on space. If it's due to natural growth of your data, then additional disk space will be needed at some point. If you got a lot of data that's no longer needed, devising some sort of archival process and then removing the data from production may be your solution. Or, if you've had one of those "unusual circumstances", shrinking files may be all you need to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.