Drive Space Disappearing Captain!

Being very mystified recently that my drive apparently kept filling up, no matter how much space I freed by file deletions, I went on the hunt for any directory et al that might be growing, uncontrolled.

My journey took me to the directory:

• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Where I found a "nnn_log.ldf" file which was over 50% of my total drive capacity.

Upon opening "SQL Server Management Studio" I found the database in question, opened the Properties dialog and clicked on Files, in the left-hand list of panels. There, under the "Autogrowth" field of the "Database files:" display was a happily chugging "By 10 percent, unrestricted growth".

Simply changing the restricted size to something sensible did not work as hitting Ok threw an error dialog up.

A quick Googling later and this showed up:

• http://www.tek-tips.com/viewthread.cfm?qid=1530206&page=1

So, to get rid of an overly large log file attached to the DB, the process is thus:

• Take DB offline
• Detach DB (Untick "Keep Full Text Catalogs", if that is the problem part)
• Delete the overly large file
• Re-attach DB, removing the LDF file from the "database details" gridview
• Click "No" to skip re-attaching the log files/full text catalog (you've deleted it)
• Open the DB's "Properties -> Files" dialog
• Under "Autogrowth" click "..."
• Untick "Enable Autogrowth"
• Click Ok

For your DB, you should now have removed any overly large files and stopped any existing files from getting any bigger. Of course, if you do need them to grow, just keep an eye on the Autogrowth properties.