If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
Examine the Log
For this demonstration, I’m going to be using undocumented function fn_dump_dblog() and undocumented trace flag 2537. The function is similar to other commands with which you may be familiar, DBCC LOG() and fn_dblog(). You can find some unofficial documentation on fn_dump_dblog() on the blog of fellow Certified Master Dimitri Furman (blog) here: Reading database transaction log with fn_dump_dblog(). Also, Paul Randal (blog|@PaulRandal) has blogged about using this function. I generally prefer using DBCC LOG() or fn_dblog() because the parameters to pass in are more manageable. The really cool thing about fn_dump_dblog() though is that it can be used to view the transaction log inside of a backup file.
Additionally, trace flag 2537 can be used in conjunction with any of the three log reader functions mentioned above to include the inactive portion of the log file when you view it. I have been told that this trace flag used to be 2536. Not sure exactly when it changed from 2536 to 2537, so if you try this on an older version than SQL Server 2008, you may need to use trace flag 2536 instead.
For this demo, I’m going to start out by creating a new database and switching to it:
Use master; -- Create Database Create Database TestBackups; Go -- Switch to database Use TestBackups; Go
Next step is to run a manual CHECKPOINT to ensure that the log is clear. At this point, we should only see 2 or 3 entries (depending on your version of SQL Server) for the checkpoint operation. I’ll use the fn_dump_dblog() function to look at the active log and verify. On a few occasions, the checkpoint will run prior to the database logging everything it needs to do for the initial creation. If you see a lot of transactions in the log, just run this step again.
-- Clear the tran log Checkpoint; Go -- Log entries should be only the checkpoint entries, 3 records Select * From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); Go
Next I create a table and fill it with some data inside of a marked transaction. When you name a transaction, you can find the start of the transaction in the transaction log by looking for the name in the Transaction Name column of the output. After running the named transaction, I will query the log file for the count of records in the log file (lots) and also for the entry for the named transaction (to show that it’s there).
-- Insert some data into a new table in a named transaction Begin Tran Tran1 With MARK 'Tran 1' Select * INTO dbo.MasterFiles From sys.master_files; Commit Go -- Lots of log entries now Select count(*) From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); -- Including our named transaction Select * From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null) Where [Transaction Name] = 'Tran1'; Go
Next, I will CHECKPOINT the database manually again to clear the log. Then a dump of the log will show that we are back to only 2 or 3 entries for the CHECKPOINT command. Next, I will enable trace flag 2537 and requery the log for the count of records and for the named transaction. Now that we can also see the inactive portion of the log, we see that there is still a large number of transactions in the log including the named transaction.
-- Clear the tran log again Checkpoint; Go -- Log entries should be only the checkpoint entries, 3 records Select * From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); -- Enable trace flag 2537 to see all log entries (active and inactive) DBCC TraceOn(2537); -- Lots of inactive log entries Select count(*) From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); -- Including our named transaction Select * From fn_dump_dblog(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null) Where [Transaction Name] = 'Tran1'; DBCC TraceOff(2537); Go
Next I switch to master database, back up the database, and then use fn_dump_dblog() to query the log file contained inside of the backup file we just created. We see that there is only a small number of records in the backup file and our named transaction is not among them.
-- Switch to master Use master; -- Backup the database Backup Database TestBackups To Disk = 'C:bakTestBackups.bak' With Init; Go -- Check log entries in backup Select count(*) From fn_dump_dblog(null, null, N'Disk', 1, N'C:bakTestBackups.bak', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); -- What about our named transaction? Select * From fn_dump_dblog(null, null, N'Disk', 1, N'C:bakTestBackups.bak', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null) Where [Transaction Name] = 'Tran1'; Go
And then finally, I drop the database because I’m done.
Use TestBackups; Alter Database TestBackups Set Single_User With Rollback Immediate; Use master; Drop Database TestBackups; Go
Summary
Earlier today, I was lurking around #sqlhelp on Twitter, and someone pointed out the blog post by Dimitri that I linked to earlier. It struck me that fn_dump_dblog() would be a good way to demonstrate that the log file in the backup file did not include the entire log file, just the part required to bring the database to a consistent state. Be sure to work through the demo code above or download the script below:
Demo script in zip format: TranLogInBackup.zip (1 KB)
Day 19 of 31 Days of Disaster Recovery: How Much Log Can a … « Quick Disaster Recovery.com
[…] Continue reading here: Day 19 of 31 Days of Disaster Recovery: How Much Log Can a … […]
Day 21 of 31 Days of Disaster Recovery: Who Deleted That Data? | SQLSoldier
[…] If you want more info on the undocumented function fn_dump_dblog(), check out the following blog post: Day 19 of 31 Days of Disaster Recovery: How Much Log Can a Backup Log. […]
Day 22 of 31 Days of Disaster Recovery: Which DBCC CHECK Commands Update Last Known Good DBCC | SQLSoldier
[…] How Much Log Can a Backup Log […]
Day 28 of 31 Days of Disaster Recovery: Recovering SQL if the Tempdb Drive Dies | SQLSoldier
[…] How Much Log Can a Backup Log […]
Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database | SQLSoldier
[…] How Much Log Can a Backup Log […]
SQL Server Trace Flag List | Senior DBA
[…] Flag : 2537 Function: Allows you to see inactive records in transactionlog using […]
SQL Server Trace Flag List – 2330 Update | Senior DBA
[…] Flag : 2537 Function: Allows you to see inactive records in transactionlog using […]
Updated: Microsoft SQL Server Trace Flag list | SQL Service
[…] Link : http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog […]
SQL Server Trace Flag List – Senior DBA
[…] Flag : 2537 Function: Allows you to see inactive records in transactionlog using […]