“SQL Server Simple Recovery Model never requires a log backup!!!” I hear you say. Well sit back, relax and let me show you that it does…… sometimes…… although it shouldn’t!

Issue Description

I ran into a confusing situation yesterday which has a published solution, but I had never heard about it. It is one of those things that should stick in your mind considering how much havoc this may cause.

I am talking about what happens if you setup your model database so that future new databases are created in a way you want. If you stray from the SQL Server defaults, then this bug might catch you out. The issue I am talking about is the recovery model of the model database. By default SQL Server has a model database set to FULL recovery and with (in my opinion) vastly outdated initial size and auto-growth settings.

My department’s policy is to set the model database to SIMPLE and to set initial size and auto-growth to something more sensible than 1MB and 10% (for argument’s sake 128MB initial size and 128MB growth).

With SQL Server 2012 (RTM and SP1), setting the model to SIMPLE looks fine on the outside, but under the covers that is not what happens. A new database will be created using model, setting the new DB to SIMPLE recovery and you won’t immediately notice any issues. However, as soon as you start running DML you can see that something isn’t quite right by interrogating sys.databases and looking at the log_reuse_desc column for the database.

You can tag along for the ride by running the following code on a TEST!!! 2012 RTM or SP1 instance

SET NOCOUNT ON
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.databases AS D WHERE name ='MyTestDB')
 DROP DATABASE MyTestDB
GO

ALTER DATABASE model SET RECOVERY SIMPLE
GO

-- Create a test database using defaults from model
CREATE DATABASE MyTestDB
GO

-- Verify that the database is in SIMPLE recovery model and log reuse is 'NOTHING'
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';

-- Create a table in the test db and add some data (creating log file entries)
USE MyTestDB
GO
CREATE TABLE dbo.TestTable
 (Col1 int NOT NULL IDENTITY (1,1),
 Col2 char(256) NOT NULL
 DEFAULT REPLICATE('a', 256))
GO

INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

-- Now look at the log reuse information again - all is fine......
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- Perform a backup of the database, because that is what we DBAs do regularly (here to nul so we don't fill up disk space)
BACKUP DATABASE MyTestDB TO DISK = 'nul'
GO

-- Add yet more test data
INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

-- Now look at the log reuse information again - oh look..... apparently we need a log backup!!!
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

As you can see from running that code block, the log reuse description is now ‘LOG_BACKUP’ which is not possible for a SIMPLE recovery database! In fact, you can run a log backup if you like too, it will run successfully but won’t clear the log out for re-use!! So if you don’t do something about this, you will either fill your log file (if it isn’t set to auto-grow), or you will fill your disk drive where your log file resides. How fast this will happen depends solely on what activity is going on in that particular database. Your monitoring processes for drive space will warn you, but if the growth is fast enough, even these early warning systems may not be enough.

Solutions to the problem

As I mentioned at the beginning of this post, there is a solution to the problem (or rather, there are multiple solutions).

1. Don’t set model to SIMPLE in the first place, making sure it is a step you make after creating databases that don’t require FULL recovery.
2. Continue with model set to SIMPLE, but incorporate a process that “fixes” your databases after creation.
3. Install at least CU4 for SQL Server 2012 SP1 or CU7 for SQL Server 2012 RTM both of which incorporate the bug-fix for this problem (along with a whole host of other fixes).

The steps required to follow option 2 would be:

• Set the affected database to FULL recovery model
• Perform a FULL backup of the database
• Set the database back to SIMPLE recovery model

Here is the code to achieve that and also prove the title of this blog post:

-- Now look at the log reuse information again - oh look..... apparently we need a log backup!!!
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- Perform the "required" log backup
BACKUP LOG MyTestDB TO DISK = 'nul'

-- The log has been cleared...... So we have FULL recovery in disguise
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- How about we fix this permanently. Change to FULL, run a backup, change back to SIMPLE

ALTER DATABASE MyTestDB SET RECOVERY FULL
GO

BACKUP DATABASE MyTestDB TO DISK='nul'
GO

ALTER DATABASE MyTestDB SET RECOVERY SIMPLE
GO

-- Now fill our table with more data and check the log reuse column again
INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

As for option 3: I realise that using RTM is something people just shouldn’t be doing, but the fact that this bug is in SP1 is a nasty one. Also, not everyone will install Cumulative Updates (CUs) because they are not as rigorously tested as Service Packs (SPs). I know of companies that have policies strictly forbidding installing CUs on production machines for this reason alone. This makes it all the more serious that it is a bug that managed to get through to SP1. Obviously, as soon as SP2 for SQL 2012 comes out (but when will that be?) this bug-fix will be available, but until that time, you have to live with CUs or other workarounds.

I have to say again, I am surprised I hadn’t heard about this issue before – it flew under my radar or was just not picked up by the “normal news channels”. Either way, I thought I’d throw this out there to help the google juices for anyone who has similar issues.

And so, that is how the SIMPLE recovery model sometimes does need a log backup……… sort of.

Have fun 🙂