The Curious Case of the Ever Expanding Log File – SHRINK!

Why is one of my system drives nearly full?

After a bit of investigating, I find the modellog.ldf file has grown to an outstanding 50Gbs! Stop traffic!

WHY!

Turns out (Thanks #sqlhelp) the model database was setup for FULL Recovery Model…OUCH!

Okay, simple enough fix! Switch that Recovery Model to Simple, no need for FULL.

Within SSMS, right click the model database (or the db in question), TASK > SHRINK > FILES

In the File Type drop down box, select Log. Ensure the file name is correct, along with the location. Thus far I have chosen  release unused space, and simply clicked OK.

Go ahead check the log now…BAM I went from nearly 50Gbs to a few kb – NICE!

Its easy in T-SQL too!

USE [DBMaint2008]
GO
DBCC SHRINKFILE (N'DBMaint2008_log' , 0, TRUNCATEONLY)
GO
Advertisements

4 thoughts on “The Curious Case of the Ever Expanding Log File – SHRINK!

  1. Hello Shaun, thanks for the comments. You are right changing the Model db to simple will effect all new databases being created, by defaulting them to simple mode. On the server this occured changing this recovery model to simple was fine, and probably a best practice for us in this situation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s