The log file is a database file that maintains a log of all the transactions done in SQL Server so that information can be accessed later to recover the database. There must exist a single log file for each database and it is possible that more than one log file can be created for a single database. The file is in .ldf file format, which ensures that users database could be brought back to a consistent state in case of system failure. And One can Read Transaction Log in SQL Server easily.
Sometimes, most of the users become wondered to know about how SQL Server database transaction log file track and what information SQL server captures in the transaction file. In this blog, we will see SQL Server function that users can use to read transaction log in SQL Server to display what entries are made for database transaction Log.
Before explaining how to read transaction log in SQL server and how the transactions are written to the database if a user has performed any database activity. SQL transaction files being the most crucial files for database recovery, need to be analyzed efficiently to understand the nature of the transaction taking place within the database. Moreover, to read data from SQL Server database transaction using fn_dblog record. However, the function requires a beginning and ending for a transaction log file. For this function, Null is the default value and it will get back all log records from the transaction file.
Note: Use SysTools Log Analyzer to track changes in database SQL Server
Creating a Test Database SQL Server
To know how this works, we will generate a database and a table to show this function. Run the below SQL server code to create a database and a table.
Now, we have created a SQL database named as “ReadingDBLog” and a table name ‘Location’. After that, users can check all information and processes that have been used by SQL server to create a database. Again, we will run the following code to check the log file for a newly created SQL database to verify what procedures and steps performed to create the database and SQL table.
We can easily view there are 339 rows that have been given for creating a dummy database and a blank table. Now, users allow to go and check the logs file by using this function to get information for all used processes for creating the database and table. Perform the below code to show the data inside the transaction log file.
For a large number of rows and columns, users can view in the above screenshot that shows the transaction database name, similarly, it will display the create table for the table creation script. However, Transaction ID will be the same for all parts of the transaction. The desired value for the transaction log file will be filled only when the transaction begins with “LOP_BEGIN_XACT” within the Operation column. It means that the transaction will begin. The operation column will decide which operation is performed such as insert, update, delete, lock and so on. By using this keyword it will become easier to understand what operation has been performed by SQL database server.v
Run DML Commands in SQL Server
Here, we will run some DML scripts to verify how data insertion, deletion or updated is logged in the transaction log in SQL Server. Using this code, users able to track how a page is allocated or de-allocated.
Again, check our database Log file and we find out that there are a number of details logged in the Transaction Log file. Therefore, the need to filter the data.
The output will be showing like the above screenshot after performing the above script.
- Let’s check how we can read the results. Users enable to view the highlighted field, which we may read as:
INSERT statement, which includes transaction ID i.e., 0000:00000449
- Started at 2016/09/26 16:30:23:50
- Inserted a row in a Heap Table ‘dbo.Location’ in pageID 0001:00000098
- Finished at 2016/09/26 16:30:23:50
Similarly, this will display this kind of details for UPDATE and DELETE Statements
Find Internal SQL Server Operations in Transaction Log
Now, we can check the internal functions of SQL Server via this function. Let’s take an example page splitting. We are capable of tracking all information about page splits like how many times page become splits, on which page and during which operation. Let’s take a look to view split page for the INSERT operation. Running the below SQL script to get all operations related to split page.
After performing the SQL command, users can see Page split happen three times from the DML activity. The first script will tell how many times the page splits occurred and the second command explains the internal processes on which SQL server running during a single page split operation.
Similarly, if the user can drop any object or even perform other database operations then, they will get logged in the transaction Log file that will be shown by using this function.
Backup Interacts with Transaction Log in SQL Server
Now, users allow running the backup script and again read the SQL server transaction Log file. Generally, run a backup on the database and then, check the transaction Log file again.
As we enable to see the number of rows that have been reduced after doing a backup and it has been minimized to 9 rows from 528. It means, the inactive part of Log file which can follow the transactions have been dumped into a backup transaction log file and the original entries from the transaction Log file have been removed. Finally, users allow shrinking the log file if required.
Alternate Solution that Users can Perform
Also, users are capable of using DBCC Log () command to read transaction Log files, but this script will not give detailed information. Users allow using trace flag 2537 to see all the logs file instead of the active Log file.
Here, we are ending with one of the server error, which creates a hurdle to read Transaction Log in SQL Server database. Moreover, we are also discussed the entire procedure to create the database using SQL commands. Well, also recommended the other solution that the user can do easily.