September 21, 2009 at 12:31 pm
I have a maintanence plan that performs DBCC before each backup... The database is set for FULL Recovery model, in order to have T-LOG backups with Fulls.
When looking through the ERROR log for SQL2005, I see the following:
2009-09-21 02:00:00.29 spid204 Setting database option RECOVERY to SIMPLE for database PrimeSLM.
2009-09-21 02:22:27.00 spid217 Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
......
2009-09-21 02:27:51.23 spid59 6 transactions rolled back in database 'PrimeSLM' (38). This is an informational message only. No user action is required.
2009-09-21 02:28:11.88 spid59 DBCC CHECKDB (PrimeSLM) WITH no_infomsgs executed by CORPORATE\SQLAgent found 0 errors and repaired 0 errors. Elapsed time: 0 hours 3 minutes 10 seconds.
....
2009-09-21 02:39:44.27 spid204 Setting database option RECOVERY to FULL for database PrimeSLM.
2009-09-21 02:40:25.78 Backup Database backed up. Database: PrimeSLM, creation date(time): 2009/07/07(09:25:50), pages dumped: 633723, first LSN: 223408:29:1, last LSN: 225959:1325:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'J:\SQLBackup\UserDBs\PrimeSLM\PrimeSLM_backup_200909210232.bak'}). This is an informational message only. No user action is required.
Now, this morning management studio was showing this database in SIMPLE mode and no T-Log backups were happening. Also, after setting the database back to full, the backup job complained that there is there was no full backup and it can't take t-log backups. I am really stumped by this, why does it need to set the database recovery to simple and then to full? Am I missing something here?
thanks in advance.
September 21, 2009 at 2:32 pm
There is some process that is running that is switching from FULL to SIMPLE. You need to find that process and understand what the process is doing and why it is happening.
Once a database has been switched to SIMPLE, you need to perform either a full or differential backup to allow for transaction log backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2009 at 1:16 am
You have the time that the recovery model change was made, check the logs and sql job history to see what ran at that point and isolate what could have made the change. sometimes simple mode is used when rebuilding indexes for performance reasons. not ideal but people do it.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 6:40 am
wow... I can't believe I overlooked something as simple as that! thank you for your help! 🙂
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply