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.