nileshamruskarsqlserver avatar image
nileshamruskarsqlserver asked


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
dbasql server 2014sql server 2008 r2logsldf
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nileshamruskarsqlserver avatar image nileshamruskarsqlserver commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.