August 11, 2004 at 2:25 am
Does anyone know why the MSDb databases on our SQL Server 2k sp2 Enterprise Edition servers should revert to Simple recovery model (they were 'Full') every time SQL Server Agent is restarted?
(The model databases are set to Full recovery model.)
August 11, 2004 at 10:05 am
This is by design. Microsoft, in their infinite wisdom, added some code to the Agent startup process to set MSDB to simple recovery mode. If you set up the right profiler trace you can see this happening 🙁
There is no way to prevent this. In our environment we have a job that is run automatically when Agent starts to put MSDB into Full recovery mode and run a full backup of MSDB to establish a recovery point.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 11, 2004 at 10:29 am
Ed,
Why switch it to Full? The only purpose of Full Recovery Mode is to be able to do Transaction Log Backups. Microsoft doesn't feel that this is necessary for the Master, Model, and MSDB databases. Why not? Because there shouldn't be many (if any) changes made to those databases and 'best practice' is to do a backup of those databases when a change is made to them. SIMPLE recovery mode allows a complete (full) backup to be made - you just can't do Log backups.
-SQLBill
August 11, 2004 at 12:40 pm
It's quite simple really, I don't want to do a full backup every time I modify a job or DTS package etc... plus msdb contains the history of all the jobs. I use the history quite a bit for various administrative reports and trending. Unless you are suggesting that I run full backup of msdb after each job completes, then I need to have it set to full to ensure I don't loose data.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 12, 2004 at 2:09 am
I use the MSDb for information about the latest db / transaction log backups on all databases (as does Enterprise Manager).
Ed. Thanks for your comments.
August 12, 2004 at 3:02 am
There are actually 2 reasons why Full recovery mode may be necessary.
1) To allow point in time recovery using transaction log backups.
2) Provide a full audit trail of all changes made to the database. This can be examined using tools such as Log PI, Entegra, etc.
Most DBAs are only concerned with 1), but recent legislation (e.g. SOX) is going to make us think far more about 2). Certainly, my organisation's audit and compliance teams already are...
Also, it is not possible to generalise about if Full recovery mode for master and msdb is needed or not. This is site-specific, and ther are a significant number of sites that would like Full recovery of master and msdb. As we all know, getting Full recovery for msdb is not easy. Unfortunately, Full recovery for master is impossible, as there is code in SQL Server to prevent transaction log backups of master from being taken.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 12, 2004 at 7:51 am
Good points about the need for log backups of MSDB. Thanks to all of you for pointing them out to me.
It's rare for us to make changes to our jobs, so we don't have the need for log backups of the MSDB - but I should have realized that others might have changes that occur more often.
-SQLBill
November 23, 2004 at 8:38 am
It sounds like we have two choices:
- Give in to MSFT. Do only simple backups of msdb.
- Set msdb to full recovery mode before each transaction log backup
I am tempted by the latter. That way the sql job history and status is automatically up to date on recovery (thereby simplifying recovery procedures). Does anyone see a disadvantage to doing so?
-Mark
November 23, 2004 at 8:51 am
Or you can have a job to "ALTER DATABASE MSDb SET RECOVERY FULL" using the MS supplied schedule "Start automatically when SQL Server Agent starts", which is what I have done.
Define second job step to backup the master database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply