x

Reading LDF URGENT

There is some issue occured in our software and we want to find the root cause. For that we need to audit our LDF file - DML operation for finding out who/when/where it happened. I have researched alot but apart from APEXSQL i didnt get any solutions. Please can some 1 suggest a open source or query to read the ldf file in detail including dml operations.

Please suggest

more ▼

asked Dec 17, 2015 at 07:14 AM in Default

avatar image

nileshamruskarsqlserver
121 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The lowest lo-tech you can do without any external tools (at least to my knowledge) is the system function fn_dblog. It accepts a start_lsn and an end_lsn and returns lots of information about what's been going on in the transaction log. To extract the DML-operations from that information isn't trivial (not even sure if it's possible to do by script), but it does give you some indications on inserts, updates and deletes and you can look at what's happened to your clustered indexes etc.

There's also the system function fn_dump_dblog which can be used to read the transaction log from transaction log backup files.

A warning about both these functions: They are undocumented. That means there's no guarantee they don't have side effects to your system. One such side-effect that I do know of is that fn_dump_dblog created three threads which it didn't release each time it was run. That is said to have been fixed in SQL Server 2012 SP1 and SQL Server 2014. Another thing about undocumented functions: A CU or SP could remove or totally change the behaviour of undocumented objects in SQL Server. Use with caution and don't have your production system rely on them. Having that said, fn_dblog and its cousin fn_dump_dblog are sometimes useful for ad-hoc exploring of the transaction log.

But if you want to see who did exactly what at which time, I would rather use a server side trace or extended events to capture that information. Or use the built-in auditing functionality in SQL Server.

Or invest some money into a log-reader if you don't want the small overhead of auditing and tracing.

more ▼

answered Dec 17, 2015 at 09:11 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

I have the ldf file Which I have to audit. I tried scripts but they dont provide you the entire dml queries. If there is any scripts on the site which can give me details how to trace existing ldf file that will be very helpful.

Dec 17, 2015 at 09:27 AM nileshamruskarsqlserver
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x139
x87
x81
x11
x8

asked: Dec 17, 2015 at 07:14 AM

Seen: 89 times

Last Updated: Dec 17, 2015 at 09:27 AM

Copyright 2018 Redgate Software. Privacy Policy