Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View Contents of a Log Backup Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 7:26 AM
Points: 6, Visits: 47
I noticed in one of my overnight scheduled log backups that it was huge 500MB for 800MB database. Usually my log backups are relatively small 30MB. I would like to view the contents of the file so that I can see what happened.

I can't seem to find a way to do this, can you tell me if there is a way to inspect the backup file to see what transactions occurred?


Thanks,

Tom
Post #1489263
Posted Wednesday, August 28, 2013 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
I'm not aware of a tool that lets you directly read a log backup file. I'm sure there's a way to do this (for example, Red Gate has a tool that lets you read a full backup file and use it to compare data & structure to a database), but I have never seen a tool that does it. The only way I can think of to even get a good guess would be to do a restore to a point in time using the log backups you have and see what has been happening on the system that way.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1489347
Posted Thursday, August 29, 2013 5:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
http://www.apexsql.com/sql_tools_log.aspx

ApexSQL Log

Order now from $1,499







The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1489654
Posted Thursday, August 29, 2013 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
Sean Pearce (8/29/2013)
http://www.apexsql.com/sql_tools_log.aspx

ApexSQL Log

Order now from $1,499





But that just reads the log. It doesn't read log backups.

Plus, you don't need a tool to read the log. There's an undocumented command, dbcc log().


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1489674
Posted Thursday, August 29, 2013 6:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
There's an undocumented command that reads the log backup too, but I doubt it will help much. Unless someone is very, very familiar with the log internals, that'll just return hundred of MB of mostly incomprehensible data.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1489679
Posted Thursday, August 29, 2013 10:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
tom 28327 (8/28/2013)
I noticed in one of my overnight scheduled log backups that it was huge 500MB for 800MB database. Usually my log backups are relatively small 30MB. I would like to view the contents of the file so that I can see what happened.

I can't seem to find a way to do this, can you tell me if there is a way to inspect the backup file to see what transactions occurred?


Thanks,

Tom


Was that about the same time that a Reindexing job or database backup ran, by any chance?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse