Shrinking databases (and log files)
Posted: Wed Jan 21, 2009 6:43 pmQuite often, people find their log files grow quite large and find managing disk space and backups with such large logs becomes problematic.
This often occurs after an upgrade, or some other event where large numbers of transactions are affected in a single atomic query.
The SQL Server data file and log file often contain slack or unused space. We've written a stored procedure to show the usage of space, and the unused space each table takes up - run the following query to see the results :
of particular note would be the unused space column returned. Why SQL Server does not always reclaim this space is not something I've ever found an answer to.
However, I have managed to find a sure-fire method of reclaiming it.
The DBCC SHRINKFILE command will try to shrink the database and log file to a specified size. All you need to do is provide the logical file name, and the desired size.
eg :
Where 'jiwadatalog' and 'jiwadata' are the logical database file names – you can determine this by looking at the database properties in management studio. The second parameter passed in is the desired file size. Don't worry about putting a figure in smaller than the actual used space, SQL Server detects this and tries to shrink the files to be smallest space possible, if you provide a file size smaller than the data requires.
This often occurs after an upgrade, or some other event where large numbers of transactions are affected in a single atomic query.
The SQL Server data file and log file often contain slack or unused space. We've written a stored procedure to show the usage of space, and the unused space each table takes up - run the following query to see the results :
- Code: Select all
exec usp_JIWA_SpaceUsed
of particular note would be the unused space column returned. Why SQL Server does not always reclaim this space is not something I've ever found an answer to.
However, I have managed to find a sure-fire method of reclaiming it.
The DBCC SHRINKFILE command will try to shrink the database and log file to a specified size. All you need to do is provide the logical file name, and the desired size.
eg :
- Code: Select all
DBCC SHRINKFILE (N'jiwadatalog' , 100)
go
DBCC SHRINKFILE (N'jiwadata' , 2000)
go
Where 'jiwadatalog' and 'jiwadata' are the logical database file names – you can determine this by looking at the database properties in management studio. The second parameter passed in is the desired file size. Don't worry about putting a figure in smaller than the actual used space, SQL Server detects this and tries to shrink the files to be smallest space possible, if you provide a file size smaller than the data requires.