Hi,

I ran into this issue today on a Microsoft SQL 2008 R2 server. A new server I’d built hadn’t had the SQL backups set yet, so the SQL log file had blown out in size (100gb in less than a month). It had used up all the disk space allocated, so doing a normal backup wasn’t shrinking the log file. The solution was to run the following query. To do this, in Microsoft SQL Server Management Studio, select the culprit database and then the ‘New Query’ button. This will bring up an area to type your query:

ALTER DATABASE dbname SET RECOVERY SIMPLE

DBCC SHRINKFILE(‘dbname_log’, 0, TRUNCATEONLY)

 Replace ‘dbname’ with your database name, and click ‘! Execute’ to run. It should look like this:

Once successfully run, your log file size should be much more reasonable.

To stop this occurring in the first place, make sure you’ve set up regular backups to your databases. Just running a backup won’t truncate the logs though, and here’s a great article on Technet explaining it:

http://blogs.technet.com/b/beatrice/archive/2008/07/24/full-backups-transaction-logs-backup.aspx

 

Good luck!