How to read the LDF file of SQL Server Database
As a DBA, we do get the strike in our mind that what is cooking in LDF file now.
LDF file is in binary format. We cannot read it as we open and read the ErrorLog file of the SQL Server.
But we have an option where we can atlease have some idea regarding what is going on in LDF file.
Like,
when is checkpoint occuring,
how many dirty pages are flushed to disk,
what is the Current LSN going on,
Who is accessing the Current LSN,
How many pages are moved or split due to Indexes
..
..
many more
Simply query gives more information here..
select top 10 * from ::fn_dblog(default, default) where [begin time] is not null
order by [begin time] desc
Above query gives you the top 10 latest records in the LDF as latest record in 1st position.
other Query:
SELECT
[Current LSN],
[Operation],
[Context],
[Transaction ID],
[Log Record Length],
[Description]
FROM fn_dblog (null, null);
Dont just run the query by removing "top Command" because if the database is in Full Recovery model then Log size will be huge and it takes more time to run consuming the resources.
Thanks
