Transaction Log Examination Tool? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Transaction Log Examination Tool?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2011-09-30 : 11:55:52

Does anyone know of a tool that would allow us to examine the contents of a transaction log backup file? I want to see what is in these TL backups that is making them so large. We are using them for Transaction Log Shipping (via FTP) and they are surprisingly large. That could point us in the direction of what we need to do on the DB to get these file sizes down.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-30 : 13:05:49

ApexSQL Log should. It's not cheap though (~$1000/licence)

--
Gail Shaw
SQL Server MVP

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2011-09-30 : 13:18:37

Ouch!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-30 : 13:19:50

You can also use backup compression (Native in SQL 2008 Enterprise and 2008 R2 Standard and Enterprise) or utilities from Redgate, Quest (LiteSpeed) and Idera (SQLSafe). Quest also has a log reader in their TOAD for SQL Server product, I don't know if it reads log backups though.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-30 : 13:38:46

I'd honestly start by profiling SQL over the time periods and correlating the traced activity with the log files

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-09-30 : 17:57:07

Probably useless single-fact shot-in-the-dark, but do you have


UPDATE MyTable
SET Col1 = 123
WHERE Col2=456

rather than


UPDATE MyTable
SET Col1 = 123
WHERE Col2=456
      AND Col1 <> 123 -- Might need to also allow for OR Col1 IS NULL

we've had a purge of those recently ... lots of unnecessary updates clogging up Tlogs and (in our case) also causing Audit records to be created.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2011-10-03 : 10:00:49

Kristen that's exactly what I am attempting to find out by examining the trn files.

Thanks everybody.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-03 : 10:51:38

SQL Profiler and a trace over the log backup periods.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources