I recently inhereted a MOSS 2007 system where the main MOSS content database had a SQL 2005 transaction log file of approx 150Gb, whereas the database was only 7Gb. Although full backups where scheduled (including shrinking of the db’s) as part of the daily maintance plan, this did not include a transaction log backup.

Inital attempts to shrink the log file failed. I was contemplating using the DETACH/ATTACH method  and then deleting the log file as a last resort due to space issues on the server. However after reading up on the procedure, I decided to persue a way to shrink the log file the ‘correct‘ way.

After reading several posts and articles I found (like many others) that shrinking the transaction log after backing it up did not shrink the overall size of the file (albeit it had 99% available free space!). Quite by acident, I shrank it again with a nominal ‘shrink file to’ and hey presto! At last the file size went back to roughly the same size as the nominal value, giving me back my server disk space. I repeated this on the other MOSS db’s and had to do the same procedure – *2 shrinking of the log files.

The next step was to figure out why the log file had grown so large. This answered itself when I went to set the autogrowth file size. For some reason the Max file size ‘Restricted file growth’ setting was set to around 2,700,00 MB.

MOSS Created SQL Databases

Further investigation on the other MOSS SQL databases revealed that when MOSS automatically created it’s SQL databases, it set the max growth file size on the transaction log files to some arbitarily large figure (way bigger than the size of the hard drive). As the previous administrator had not checked these settings the log’s grew out of all proportion.

Possibly related posts: (automatically generated)