Transaction log file size question

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  August 19, 2011 at 9:46 am

  #1370847

  Along with that shrink, make sure you account for VLFs. Check the article in my sig list by Kimberly Tripp

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  August 19, 2011 at 9:50 am

  #1370851

  Guras (8/19/2011)

  ---

  I ran the DBCC openTran(mydb)

  but there is no open transactions.

  How do I to locate queries that consume a large amount of log space?

  Thanks for your help.

  Beyond doing a trace, I provide a handy solution for that in the article I referenced. Using the method I show, you don't have to be running a trace and you don't have to be watching the server at the time the offending process happens. 😉

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Guras

  SSCertifiable

  Points: 6518

  More actions

  August 19, 2011 at 11:36 am

  #1370920

  Thank you all for the replies, they all have been a great help. After reading all the information I have decided to SHRINK the t-log file (and I uderstand that the t-log back up chain will be broken)

  Here is the plan I am planning to undertake

  1.Find the off peak hour ( mostly late at night) and bring the databse to the SINGLE USER MODE

  2.Do a regular t-log back up.

  3. Shrink the file to the samll size as possible by running

  DBCC SHRINKFILE(mydblogfile_log,TRUNCATEONLY)

  4.Alter the databse to modify the transaction log file to a size

  ALTER DATABASE mydatabase

  MODIFYFILE

  (NAME = mydblogfile_log, SIZE = 64MB)

  5. Perform a full back up

  6. Check that the regular t-log back up is running

  Please let me know if I am missing out on some important step. Thanks a lot for all the help. This forum is really great!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 19, 2011 at 11:50 am

  #1370927

  You don't need single user mode, and nothing in that set of steps will break the log chain.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass