HUGE Log File?

• zippy-476815

  SSC Veteran

  Points: 247

  More actions

  September 6, 2007 at 3:09 pm

  #178330

  Hey everyone!

  A client of ours uses the same instance of SQL 2005 for another vendor's application, and for some reason, their log file is 29GB!!!

  Is there a good strategy to back this up and prune it regularly, or should I tell them they have to live with it and put my headphones back on?

  We don't typically support this sort of thing, but it's eating disk space and I believe the other vendor has not responded to support contact in over a week!

  Thanks in advance!

  Mike

• webrunner

  SSC-Dedicated

  Points: 31866

  More actions

  September 6, 2007 at 4:40 pm

  #731994

  Look at the question I posted and the discussion. The log file you describe is almost 10 times bigger than the one we had.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=357&messageid=361719#bm364856

  The raw size of the log file can become an issue, but is not necessarily the main problem. The problem is making sure that there aren't things growing the file that don't have to (such as re-indexing jobs and such).

  Best,

  webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  September 6, 2007 at 4:43 pm

  #731995

  Hey zippy,

  There are literally hundreds of posts just like this.

  the skinny is, should their log be 29GB? Probably not.

  Is there something that can be done? Well yes, but the answer is not cut and dry.

  There are several things that should be considered, What is the recovery model? what is the frequency of the backups? what is the failover plan?

  Sure you can go in and truncate the log, and shrink it, but it will keep coming back. so have the client Figure out the backup strategy, and recovery strategy. this will help them set the recovery model. and as long as the backups are going off regularly the log shouldn't go out of control.

  For a log to be this size, either its a huge DB with alot of transactions per day, or it has alot of bulk loaded data and the recovery model is not set right, or its not getting backed up frequently enough.

  Lots of Info to read to help decide backups and recovery model

  http://msdn2.microsoft.com/en-us/library/ms187048.aspx