MS SQL 2008 shrinking database and log file disk space usage
Posted on 2013-09-24
I am trying to set up a proper regular routine for shrinking database files. The main reason I want the shrinking to be put in place is that my log file usage is ridiculously low (e.g. 0.5%), means the log file uses gigabytes on the disk whereas stores only megabytes of the log data only.
This is my configuration:
SQL Server 2008 64-bit
Database compatibility level 80 (SQL Server 2000)
Recovery mode = Full
To be able to effectively use SHRINKDATABASE or SHRINKFILE I need to switch the database to simple recovery mode. After performing dbcc SHRINKDATABASE('mydbname', TRUNCATEONLY), the log file size decreases from 4GB to couple MB, well so far it works the way I expect it work. But now it starts to get interesting. I switch the db back to full recovery and as soon as a first user logs in and starts creating DML transactions the log file grows back to the size of gigs, i.e. whilst the log file stores a few transaction logs only its size is enormous.
Any ideas of how to achieve an optimal log file disk space usage?
Thanks in advance for sharing your thoughts on this!