SQL Log file

Digital Devices

How to read Database transaction log in SQL Server?

Another frequently asked questions in the forums and newsgroups, I have seen similar ones recently. I believe most of the newbie users ask this question and even more as interview based questions.
So here is the catch in order to accomplish the task, say you need to provide a solution to log and show all Create, read, update and delete (CRUD) transactions for auditing, this is more a mandatory for SOX & HIPPA regulations.
Using the standard SQL Server methods which is an undocumented statement DBCC LOGINFO will get information, but the problem is it cannot be interpreted in simple terms as per the requirement. Hence the rise of third party tools in this area is evident. In order to enable to read SQL Server transaction log files and provide an in-house tool to show the CRUD transactions on a set of databases in a customized manner is a demanding task for a DBA (now a days). The other aspect is to use Server side trace (Profiler) to track the statements on the database (which will impact the performance if the server is already stressed out for resources)
As per the note above using undocumented DBCC command you will get:

DBCC LOG(databasename,{0|1|2|3|4}])
0 - Basic Log Information (default)
1 - Lengthy Info
2 - Very Length Info
3 - Detailed
4 - Full
Example:
DBCC log (Databasename, Type)
And it displays the following transaction log information:

• Current LSN
• Operation (string starts with LOP_XXXXX)
• Context (string starts with LCX_XXXX)
• Transaction ID
• Tag Bits (Hex)
• Log Record Fixed Length (int)
• Log Record Length (int)
• Previous LSN
• Flag Bits (Hex)
• Description (string)
• Log Record (Hex)

As it is undocumented on the Microsoft articles its very hard to interpret what lies beneath those HEX values, therefore using third party tools such as APEX-SQL Log Reader and Lumigent's Log Explorer are more helpful to such users that are needed to cater the management needs. I believe Microsoft has to invest something heavy here in order to comply the newest industry regulations for Auditing and monitoring purpose.


SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%%'