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
Sql-server – Freeing unallocated space
disk-spacesql server
Related Question
- Sql-server – Disk space full but logical space available in database
- Sql-server – Free up disk space on sql server
- Sql-server – Get MS SQL Server to release disk space
- Sql-server – Why can’t I get the available free space after shrinking
- Sql-server – MS SQL Server 2008 makes hard disk space disappear
- MySQL used all drive space running an ALTER and now won’t start
- Sql-server – Reclaim disk space from large database’s primary filegroup after truncating tables (other than shrink)
- Sql-server – Release unused space in SQL Server database
Best Answer
The problems with shrinking database files are primarily:
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.