MSDB Recovery Model

Question

MSDB Recovery Model

Nilssond

Hall of Fame

Points: 3285
More actions
July 7, 2005 at 11:39 am

#102629

What is considered BEST PRACTICE for setting the recovery model for MSDB?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

Assuming you're talking about SQL Server 2000, I'd say leave it in simple recovery mode as that's the way it ships and installs. IIRC, even if you change it to full it will switch back to simple after a restart. So if you want to keep it a full, you'll have to set up a start-up script to change modes back when the server boots up.

My hovercraft is full of eels.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Since the change from Simple to Full requires a full backup of the DB before the logs can be used, you would also have to perform the backup in this startup script.

So I guess you could call leaving it a Simple is an enforced Best Practice?

Andy

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

I tend to agree, though if you have some auditing requirement about the information that's stored there (job history, backups, package changes, etc), then you might want to move to full.