Changing Database Recovery Model with Open Transactions

System Setup

We use

Create a folder to contains backups

MD C:\Backups

Create a database in full logging mode

CREATE DATABASE justdave2;

Take a full database backup

BACKUP DATABASE justdave2 
 TO DISK='C:\Backups\justdave2-full1.bak'
 WITH INIT,FORMAT;

We now create a table,insert a row and take a log backup

USE justdave2;
CREATE TABLE job (client_id INT);

-- First timestamp 2017-05-10 07:13:01.120
SELECT CURRENT_TIMESTAMP;

WAITFOR DELAY '00:00:02';

BEGIN TRAN empty_table;
INSERT INTO job VALUES(1);
COMMIT TRAN;

BACKUP LOG justdave2 
 TO DISK='C:\Backups\justdave2-log1.bak'
 WITH INIT,FORMAT;

Next in a second session we start a transaction and insert a row into the table to keep the transaction open

USE justdave2;
BEGIN TRAN second_insert;
INSERT INTO job VALUES(2);
COMMIT TRAN;
BEGIN TRAN third_insert;
INSERT INTO job VALUES(3);

In the first session we check for open transactions and change to the to BULK LOGGED recovery model

DBCC OPENTRAN();

In the first session we change the database justdave2 to BULK LOGGED recovery model

 ALTER DATABASE justdave2 SET RECOVERY BULK_LOGGED;

In the second session we now perform a minimally logged operation and commit

SELECT * 
  INTO job2 
  FROM job;
COMMIT TRAN;
DBCC OPENTRAN();

In the second session we now perform a fully logged operation and commit

WAITFOR DELAY '00:00:02';

-- Second timestamp 2017-05-10 07:16:36.220
SELECT CURRENT_TIMESTAMP;

BEGIN TRAN fourth_insert;
INSERT INTO job VALUES(4);
COMMIT TRAN;

DBCC OPENTRAN();

In the first session we now backup the log again

BACKUP LOG justdave2 
 TO DISK='C:\Backups\justdave2-log2.bak'
 WITH INIT,FORMAT;

We restore to a point in time using the first log backup

-- In both sessions
USE master;

-- In the first session
RESTORE DATABASE justdave2
 FROM DISK='C:\Backups\justdave2-full1.bak'
 WITH NORECOVERY,REPLACE;

-- Restore stopping at point in time at the first timestamp above, works fine.
RESTORE LOG justdave2
 FROM DISK='C:\Backups\justdave2-log1.bak'
 WITH STOPAT='2017/05/10 07:13:01',
 NORECOVERY;

-- Bring the database online
RESTORE DATABASE justdave2 WITH RECOVERY;

USE justdave2;

-- Get back 0 rows
SELECT COUNT(*) FROM job; 

We now try a point in time restore using the second timestamp

-- In the first session
USE master;

RESTORE DATABASE justdave2
 FROM DISK='C:\Backups\justdave2-full1.bak'
 WITH NORECOVERY,REPLACE;

RESTORE LOG justdave2
 FROM DISK='C:\Backups\justdave2-log2.bak'
 WITH NORECOVERY;

RESTORE LOG justdave2
 FROM DISK='C:\Backups\justdave2-log2.bak'
 WITH STOPAT='2017/05/10 07:16:36',
 NORECOVERY;

RESTORE DATABASE justdave2 WITH RECOVERY;

USE justdave2;

SELECT COUNT(*) FROM job;

This fails as the log backup contains minimally logged transactions

We use fn_dump_dblog to examine the log backup and see log entries which set bits in the ML (minimally logged) map.

As we know there was only 1 transaction running at a time we can tell the bits were set by transaction "third_insert"

SELECT [Begin Time],[Transaction ID],[Transaction Name],*
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
,'C:\Backups\justdave2-log2.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 (
([Operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT')
)
OR
([Operation]='LOP_SET_BITS' and Context='LCX_ML_MAP')
)

We can examine the log backup for just the entries which set bits in the ML map

SELECT [Begin Time],[Transaction ID],[Transaction Name],[Page ID],[Slot ID],
[PartitionID],[Rowbits First Bit],[Rowbits Bit Count],[Description]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
,'C:\Backups\justdave2-log2.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 ([Operation]='LOP_SET_BITS' and Context='LCX_ML_MAP')

We can see

• The transaction id is 0000:00000000
• The page id is 0001:00000007 is file 1, page 7
• The partition id is null as there is no associated data/index partition
• We can see the first bit set by each log entry
• We can see only 1 bit was set by each log entry

We confirm the database has been switch to bulk logged mode by the log restore

We can use DBCC PAGE to dump page 1:1 from the database and see the page 1:7 is an ML_MAP page

USE justdave2;

-- Set output to the console
DBCC TRACEON(3604);

-- Dump the 1:1 page with just the page header
DBCC PAGE (justdave2,1,1,0);

We can dump the page 1:7 in detail and see that after the restore no bits are set in the ML_MAP page

-- Dump the page header plus detailed per-row interpretation 
DBCC PAGE (justdave2,1,7,3);

We perform a new minimally logged operation and recheck the set bits in the ML_MAP page

We see that a bit has been set in the ML_MAP

SELECT * 
  INTO job3 
  FROM job;

-- Dump the page header plus detailed per-row interpretation 
DBCC PAGE (justdave2,1,7,3);