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