msdb database keep switching to Simple Recovery Mode ? 2

[GJP55]

My msdb database keeps switching from full recovery Model to simple recovery model. This has happened twice last week.

Anybody got any idea's ?

Thanks

 

[JayKusch]

Give this a try!!!

First w/ MSDB set as FULL Recovery ... stop and restart SQL Server. 10 to 1 it will have reset the FULL back to Simple. This is tied to the issue of 'trunc. log on chkpt' being on.

SO lets do this ... Run this code in Query Analyzer:

use master
go
EXEC sp_dboption 'msdb', 'trunc. log on chkpt', 'false' 
go
sp_dboption 'msdb' 
go

Now Stop and Restart SQL Server.

Now set MSDB to FULL mode once again.

Stop and Restart SQL Server.

Recheck that MSDB is STILL set to FULL.

You should be good to go!


Thanks

J. Kusch

 

[GJP55]

Thanks for your reply. Will try this after office hours. We have had to stop and start SQL Server twice this week as well so that must have been why.

Thanks for you help, much appreciated.

 

[technome]

Jay..
Thanks for the code

 

[mrdenny]

The MSDB database should be in simple recovery mode. There is no need to keep the transactions.

All system databases should be kept in simple recovery mode.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)