TX Logs

• Carl Farmer

  SSCrazy

  Points: 2197

  More actions

  November 6, 2002 at 3:44 am

  #58677

  Can anyone help?

  I am looking to measure the size of the TX logs on several db's. What I would like to do is every 58th minute of the hour, record the size of the TX log before it is truncated and backed up. Does anyone know of a method that can do this??

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 6, 2002 at 4:09 am

  #440466

  This will return the size of the Trans Log

  select cast((size / 128.00) as decimal(10,2)) as tl_size from dbname.dbo.sysfiles where status & 66 = 66

  Works with SQL 7 and 2K.

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  November 6, 2002 at 7:55 am

  #440475

  Correct me if I'm wrong, but I think the select statement Antares686 provided will give you what you ask for the allocated size of the transaction log, but I think you want to know how much of the allocated transaction log space is used.

  I would suggest you consider trying use the output from DBCC SQLPREF(LOGSIZE) and taking the " Log Size (MB)" and the "Log Space Used (%)" to calculate the current size of the used portion of the transaction log.

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  November 6, 2002 at 8:21 am

  #440478

  Gregory, I think you mistyped the command - try

  DBCC SQLPERF(logspace)

  instead.

  Steve Armistead,

  Database Administration

  Panther Systems Northwest, Inc.

  Vancouver, WA

  Steve

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  November 6, 2002 at 8:25 am

  #440481

  Ooops. Thank you for correcting my typo!!!!

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP