Reading the .LDF File in Sql server 2005

Question

Post reply

Reading the .LDF File in Sql server 2005

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
March 1, 2010 at 7:44 am

#145532

Hi,
I would like to read the contents of the .LDF File in Sql server 2005, is there any way or tool, if so please let me know.
With Regards
Dakshina Murthy

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Shriji SSCommitted Points: 1881 More actions · Answer 1

Is it possible for you to share the objective of the requirement?

By the way LiteSpeed 5 and above can do the job. www.quest.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Why? What are you trying to achieve here?

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

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 3

The objective or the requirement is, we would like to know what all the transactions has happend which is stored in the .ldf file and its contents in .ldf file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If you want to track transactions and changes, you're better off using triggers or a SQL trace. The transaction log was never intended to be human readable, if you want to do that, you'll need to buy a 3rd party log reading tool. those generally go for around $1000 per instance.

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

GSquared SSC Guru Points: 260824 More actions · Answer 5

There's an undocumented DBCC command that can read the log file. The data it provides may or may not do what you need.

It's "DBCC Log()". The parameters that I know of are the database name and a numeric "style" that determines the columns you get back.

For example, "DBCC Log(master)" will give you the log data for the master database, in the default format.

If you want to change the columns returned, try "DBCC Log(master, 2)". The number can aparently be -1 through 4.

Be aware that it can return a LOT of data on an active database or one where it's been a long time since you did a log backup.

This is undocumented, which means side-effects, etc., are somewhat unpredictable. I do know that SQL 2008 has one extra column compared to SQL 2005 when you use the default. I don't know if there are other differences.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

GSquared (3/1/2010)
It's "DBCC Log()". The parameters that I know of are the database name and a numeric "style" that determines the columns you get back.

SELECT <column list> FROM fn_dblog(null, null) does much the same thing. It's not particularly easy to understand though

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

GSquared SSC Guru Points: 260824 More actions · Answer 7

The function uses the DBCC command, if I'm not mistaken.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Probably. I prefer it, as I can select columns, filter, etc without a temp table.

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

GSquared SSC Guru Points: 260824 More actions · Answer 9

The last use I had for either, I found the function was much slower than the DBCC command. In the case I was working on, that mattered enough.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

I highly recommend ApexSQL Log and Recover.

Disclaimer: I have a close relationship with Apex, use their products and recommend them to my clients. Also, if you care to you can mention TheSQLGuru sent you you will get a discount and my daughter will get a few coins for her college fund.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 11

GilaMonster (3/1/2010)
GSquared (3/1/2010)
It's "DBCC Log()". The parameters that I know of are the database name and a numeric "style" that determines the columns you get back.
SELECT <column list> FROM fn_dblog(null, null) does much the same thing. It's not particularly easy to understand though

Hello,

This will tell you how the insert, update or deletes are occurring in your T log.

SELECT [Previous LSN],[Current LSN],[Operation],[AllocUnitName],[Page ID],[SPID],[Begin Time], [Transaction Name],[Lock Information] FROM ::fn_dblog(null, null)

GO

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

dakshinamurthy-655138 (3/1/2010)
The objective or the requirement is, we would like to know what all the transactions has happend which is stored in the .ldf file and its contents in .ldf file.

Ok... lots of folks have helped you find a solution and it's kind of obvious that you want to "know what all the transactions has happend "...

Would you mind telling us why you need to "know what all the transactions has happend"? I mean, what is the business reason and what are you hoping to achieve by doing this?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

Looks like this might be a dead thread now Jeff...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 14

Sorry,

for not replying to the thread, as i was not feeling well i was off from the office.

The reason why i need this is, one of my client need to know the actions happend on the data and need to audit that or cross check the things what has happend and when. Thats the reason why we wanted to know.

The tool ApexSQL which is mentioned in the thread, i tried with the 14 days trial version on my local database and it has really helped me out. Thanks a lot i will be using the same on my Live database.

Regards

Dakshina Murthy