Posts

Showing posts with the label transaction log

fn_dblog( ) function documentation

Goal: This post provide some documentation for the fn_dblog() function. Key Points:     * view transaction log of a database (if you want to view the transaction log of a log backup, use fn_dump_dblog function instead.)     * useful for:           > restore database to a certain point in time           > find out when an operation (page split, table delete, table truncate...).    * take 2 parameters, start time and end time.    * It's not recommended use this function to scan the transaction log of a production database because it can reduce performance due to increase disk I/O. -- get fn_dblog() definition sp_helptext 'sys.fn_dblog'; --result  create function sys.fn_dblog  (   @start   nvarchar (25) = NULL,   @end     nvarchar (25) = NULL  )    returns table as  return ...

SQL Server Transaction Log

Goal: understand SQL Server Transaction Log architecture. This topic is complex, and I'm no expert; that's why I've attached several links from book online and other experts. Before going to details about backup and restore. We must understand the basic of the SQL Server Transaction Log architecture. For more details, please see Paul Randal's post here .  Key Points: * Every database has at least one transaction log (yes, including tempdb) * Every record in a transaction log is identified by a LSN (Log Sequence Number). LSN is unique 10 bytes in size (4 bytes for VLF number + 4 bytes for block number + 2 bytes for log records number) , it's like a time stamp. Ex: 0000002e:0000027a:0001 * Realistically, it's not possible to use all LSN. Kimberly Tripp and Paul Randal provide an excellent example here . Based on the example, it would take 240 million years to generate 4 billion petabytes transaction log. * Log records are the changes made to a data...