SQL Server has a categorized storage defined for all its data. Databases are stored and maintained as MDF and NDF files respectively, which denotes primary and secondary database each. On the other hand, there is a Transaction Log database file for every master database. The job of a transaction log file is to store all activities performed on the respective database as part of a record. In this article we will know how to view SQL Server transaction logs using fn_dblog() function and other mechanism.
What Information Stored In a SQL Log File?
The log file acts as a record that maintains each entry, i.e. query executed on the database by a user. This is to keep a record as well as rollback whichever change made is undesired to the user or is made mistakenly. Log files are also important from security point of view, as one can target all changes or modifications made to the database through it along with the duration at which they were made. Thus, this information comes in great help during data investigations or server analysis. However, it is very important to truncate the log regularly so that it doesn’t get stacked up. In order to keep a check on the transaction log file, it is necessary to look at what it logs in it and what all transactions are taking up too much space unnecessarily. This allows administrators to see what all transactions can be avoided from being logged on to the file. To know how to view SQL Server transaction log, the following article may come in great help to the server admins.
Learn How to View SQL Server Transaction Log File
This section of the segment offers detailed information about the transaction log file reading on SQL Server. Follow the steps in the same sequential order to attain desired results and be able to view SQL transaction log file entries:
NOTE: However, note that the transaction-storing log files are crucial from administrative and security standpoint. Therefore, it is necessary to keep the file secure in the process and not let it turn into an inconsistent state or be deleted/moves unless the consequences are completely known.
Method to View SQL Server Transaction Log
Microsoft features an undocumented function, which is known as ‘fn_dblog’ and is known for reading Transaction Log file contents. The file consists of crucial information regarding the happenings taking place in one’s database. Therefore, reading it might come as a requirement to many.
TIP: Before implementing any undocumented function, it is strongly recommended that it be used in a testing environment or a test database first.
Function or the fn_dblog needs the starting and ending Log Sequence Number to begin with. NULL, however, is the default value for this function to which all log records stored within a transaction file are returned by the server.
- Run the code given below for determining the log file of the database you require. This will let you know the steps carried out during creation of the particular database and its table(s).
- You will now be able to see the number of rows within the database table. The same function can be used for checking the log entry for the steps involving during the creation of the database and its table.
- The above coding when used, generates results as shown below in a representational screenshot of a database named ReadingDBlog.
This section describes how to view SQL Server database transaction log file. However, there are more things that you can do for the same, i.e. using DBCC Log() or trace flag 2537. These commands are also meant for reading / displaying transaction log information. Nevertheless, DBCC Log() doesn’t offer the information in detail and the latter lets you look at all the log files maintained on the server and not just the active ones in particular. In such situation you can take the help of third party SQL Log File Viewer which allow you to view all transaction activity saved in log file.