Changing Recovery Model from Simple to Full | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Changing Recovery Model from Simple to Full

Our database has a full recovery model. We have a set of scheduled SQL jobs that change our db recovery model to "simple", then, after other scheduled SQL jobs are run (indexing, update stats, etc.), another job kicks off to set the recovery model back to "full" before running the database backup job. The job to set the recovery model back to "full" failed the other day, and now the backup job cannot run. I tried to manually change the model back to "full" through Enterprise Manager and through Query Analyzer, and I receive the same error message: Server: Msg 3023, Level 16, State 3, Line 1 Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. However, when I try to do a database backup, it just keeps running and nothing happens. After about an hour with no progress, I killed the backup job. Has anyone seen this before, and how does one fix it? Any help is greatly appreciated! [:I] qchua
Are you using database maintenance plans?
You can take advantage of BULK LOGGED RECOVERY model in this case and ensure to schedule the job to change recovery model once the previous job is finished. Refer to the books online for more information on RECOVERY models topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>