tlogs

For most activities in MS SQL (not including queries), information on the action is logged.  depending on the database, the action is logged to that database transaction log.  this potentially allows for rollbacks or point in time restores if something is damaged.

When SQL is installed, you can specify the location of the transaction logs; you can also move the logs after the database comes on-line.  Since transaction logs are very write-oriented and can prevent anything else from happening, these logs need to be on very fast storage (low latency and high i/o disks – RAID 1 or 10 or SSD).  Even tempdb has a transaction file.

There is an undocumented function [fn_dblog(null,null)] to look at a log file from one starting LSN to the ending LSN (null is the default).  You can also use DBCC Log() and  trace flag 2537. to look at logs.  There is an example in MS SQL Tips

USE ReadingDBLog;
GO
select COUNT(*) from fn_dblog(null,null)

will count the number of entries in the specified log file.  To look at the data, use

USE ReadingDBLog;
GO
select [Current LSN],
       [Operation],
       [Transaction Name],
       [Transaction ID],
       [Transaction SID],
       [SPID],
       [Begin Time].
       [CONTEXT]
       [AllocUnitNume],
       [Page ID],
       [Slot ID],
       [Begin Time],
       [End Time],
       [Number of Locks],
       [Lock Information]
FROM fn_dblog(null,null)

There are more than 100 columns available in the table. If you backup the database, and count rows in the tlow, you will see the number of rows decrease, assuming that transactions have been committed, and the inactive entries removed.

Advertisements