Is it possible to set MSDB's recovery model to Full?

  • Article

The following topic in SQL Server 2000's documentation “Backing Up the model, msdb, and distribution Databases” (http://msdn2.microsoft.com/en-us/library/aa173541(SQL.80).aspx), and the equivalent in SQL Server 2008 “Backing Up the model and msdb Databases” (http://msdn2.microsoft.com/en-us/library/ms188274.aspx) recommend to set the recovery model of msdb to Full, under the circumstances specified below.

  • If you use the backup and restore history information in msdb when recovering user databases, it is recommended that you use the Full Recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.

But then, you may have noticed already that, in SQL Server 2000, every time SQL Server Agent starts, it sets MSDB's recovery model back to Simple, because it enables "trunc. log on chkpt."

In SQL Server 2005 this functionality has been changed, and replaced it with an error handler within the function that executes T-SQL against the instance of SQL Server associated to that instance of Agent. The error handler code will truncate MSDB's transaction log when SQL Server throws a 9002 in response to the query it just executed (SZ_SQL_TRUNCATE_MSDB_LOG).

So, the bottom line is that the documentation is not correct at the moment. It isn't supported to set the recovery model of MSDB to something different than Simple.

In SLQ Server 2000, if you trace SQL Agent's activity using Profiler, you'll notice that it issues the following batch, every time it starts up:

IF (NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE (name = N'msdb') AND (status & 8 = 8))) EXECUTE master.dbo.sp_dboption N'msdb', N'trunc. log on chkpt.', true

In SQL Server 2005 though, you can change MSDBs recovery model to bulk-logged or full, but as soon as it receives a 9002 as a result of the execution of any T-SQL statement, you'll see in the profiler trace that immediately after the 9002 is thrown, it issues the following batch:

DUMP TRANSACTION msdb WITH NO_LOG

and it will report (to SQLAGENT.OUT) the log truncation and the SQL command which necessitated it.

The msdb log was truncated after the following SQL command caused it to become full: %s

Related information in the following KB article 257856.

Keep tuned.