MSDB recovery model changed on its own ???? 1

[keithinuk]

I received an email alert this morning that the SQL 2000 transaction log backup for MSDB had failed. It has been running successfully for 7 weeks now.
On investigation I discovered that the recovery model had changed from Full to Simple. I haven't changed it and I know nobody else has. They're all on holiday this week so I'm on my own.
Any ideas please ?????????????

 

[EdVassie]

According to Microsoft (who probably should be trusted on this...) every time SQL Agent is stated, it will set the recovery mode of MSDB to Simple in SQL2K, or in SQL7 it will set 'Commit at Checkpoint' on.

There is no way to prevent this behaviour. You have to design your backup and recovery strategy around this.

Some people run a sp at SQL Server start time to reset MSDB to allow transaction logging. Others use a job at SQL Agent start to do the same thing (more reliable).

Personally, I take a full backup of MSDB every hour, just after I have taken transaction log backups of other DBs. This posting is a personal opinion only, and may not reflect reality.