Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

T-SQL Tuesday #12 - Misconceptions

TSQL2sDay150x150 It’s time for T-SQL Tuesday again, and I’m happy to participate again. This is a monthly blog party started by Adam Machanic (Blog|Twitter )that asks people to write on a particular topic all on the same day.

This month’s theme is misconceptions in SQL Server, hosted by Sankar Reddy. You can read the rules for the party and get information from his blog.

The Most Common Misconception

I read a lot of forum posts, literally hundreds a week on all topics in SQL Server. One thing that I consistently see asked is the dreaded:

“My transaction log has grown so large it filled the disk”

That’s a common occurrence, too common in my opinion, and while there are people that forget to setup any backups, I also find in the majority of cases people just don’t understand one thing:

A full backup does not clear the transaction log.

Too many people assume that the log will get managed by a full backup. It doesn’t. While some log record get included in a full backup, they do not get marked as “backed up” and the space re-used.

If there is one thing that I wish everyone managing a SQL Server knew, it would be that they need to make full and log backups to properly manage the disk space usage by their logs.

Comments

Posted by Jason Brimhall on 12 October 2010

Good one.  That is a good thing to remember.

Posted by dennisparks on 14 October 2010

(when appearing at the scene of the accident)

my old tried and true to get the log small is:

switch the db to simple, do a full backup, then run a dbcc shrinkfile on the log, then switch back to full and have someone setup backups/log backups.  Tell them not to do that again.  :~)

Posted by Chevalier.Paul on 14 October 2010

If you only need full and differential backups just leave your recovery model to simple.

Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files.

Posted by JohnB on 14 October 2010

Chevalier.Paul, are you sure this is the case? I just had to deal with a case where the recovery model was set to Simple but the transaction log continued to grow after the nightly backup. I had to manually shrink the log.

Posted by Steve Jones on 14 October 2010

If your recovery model is simple, then you don't need transaction log backups. The log doesn't grow unless you have an open transaction that prevents checkpoints from marking portions of the log as inactive.

However most people don't understand what simple means and how that affects your DR capabilities. I don't think that most people should run in simple mode until they do understand the risks.

Posted by Brnbngls on 14 October 2010

I've got a daily full backup, differential every 4 hours and tlog backups every 15 minutes on one of our production db's and the log file is still hitting 15GB after a day of use.  Not sure what I'm doing wrong.

Posted by Steve Jones on 14 October 2010

I would suggest you post something in a forum or engage a consultant. The log might need to be 15GB for your system. Size depends on activity and log backups.

Posted by JQA on 14 October 2010

Brnbngls,

How large is your database?  Do you really need to do differentials? Why not simply a daily full along with your tlog backups?  It would make your recovery much simpler.

JQA

Leave a Comment

Please register or log in to leave a comment.