May 6, 2011 at 2:04 pm
Hello,
I have a maintenance plan that has been running for over a year. Lately, I have been getting an error.
I have a maintenance plan that
1) Does a differential backup
2) Sets the recovery model to simple
3) Checks integrity
4) Reindexes
5) Checks integrity again
6) Does a full backup
However for the last few days, I've been getting an error message:
Executing the query "alter database KRONOS set RECOVERY simple" failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've been trying to search the internet for reasons why an "alter database set recovery model to simple" is now causing an error. I haven't seen anything yet.
Does anyone know why this could cause an error?
I am running on this server SQL Server 2008 sp2.
Thank you.
Tony
Things will work out. Get back up, change some parameters and recode.
May 6, 2011 at 2:06 pm
when does the recovery model ever change on the database to cause you to have that step?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 6, 2011 at 2:56 pm
Sorry about that...I left out a step.
1) Does a differential backup
2) Sets the recovery model to simple
3) Checks integrity
4) Reindexes
5) Checks integrity again
6) Sets the recovery model to full
7) Does a full backup
Thanks.
Things will work out. Get back up, change some parameters and recode.
May 6, 2011 at 5:39 pm
WebTechie38 (5/6/2011)
Sorry about that...I left out a step.1) Does a differential backup
2) Sets the recovery model to simple
3) Checks integrity
4) Reindexes
5) Checks integrity again
6) Sets the recovery model to full
7) Does a full backup
Thanks.
First thing I want to ask is whether or not you are aware of the fact that you are breaking the log chain and could find yourself in a position where you cannot recover the system to a point in time?
Just for the sake of argument, let's say you have an issue with your system some time after the full backup completes. Okay, you try to restore the system and find that the latest full backup is corrupted. Now, you go to the previous full backup - restore it, get the latest differential and restore that - and then try to apply the latest transaction log backups...oops, won't restore because you broke the log chain.
Now, you have lost all data from the latest differential that you took right before the maintenance all the way up to the point in time of the failure. And no, you cannot apply the differential backup you did after the latest full backup (and just before the crash) because differential backups are tied to the full backup.
As for why you are getting this error, it is rather simple. There is another process running at that time that is blocking the alter statement. It could be another backup being run outside your maintenance plan.
Now, if you find that there is another utility being used to backup the server - you have additional issues. If that backup is not being done as a copy only backup, it is breaking the differential chain and you will not be able to restore the differential backups until you find the backups taken by this utility.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 6, 2011 at 6:02 pm
Hi Tony,
Might be a silly question, but have you checked that your transaction log backup job/maintenance plan is not trying to run at the same time?
Cheers,
Jason
May 7, 2011 at 3:15 am
Do you have any changes in the backup plan like TAPE backup running at the time of your maintenance plan ?
Check the Schedule time of Backup and Maintenance plan job at the same time ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 7, 2011 at 2:48 pm
Jeffrey Williams-493691 (5/6/2011)
WebTechie38 (5/6/2011)
Sorry about that...I left out a step.1) Does a differential backup
2) Sets the recovery model to simple
3) Checks integrity
4) Reindexes
5) Checks integrity again
6) Sets the recovery model to full
7) Does a full backup
Thanks.
First thing I want to ask is whether or not you are aware of the fact that you are breaking the log chain and could find yourself in a position where you cannot recover the system to a point in time?
Just for the sake of argument, let's say you have an issue with your system some time after the full backup completes. Okay, you try to restore the system and find that the latest full backup is corrupted. Now, you go to the previous full backup - restore it, get the latest differential and restore that - and then try to apply the latest transaction log backups...oops, won't restore because you broke the log chain.
Now, you have lost all data from the latest differential that you took right before the maintenance all the way up to the point in time of the failure. And no, you cannot apply the differential backup you did after the latest full backup (and just before the crash) because differential backups are tied to the full backup.
As for why you are getting this error, it is rather simple. There is another process running at that time that is blocking the alter statement. It could be another backup being run outside your maintenance plan.
Now, if you find that there is another utility being used to backup the server - you have additional issues. If that backup is not being done as a copy only backup, it is breaking the differential chain and you will not be able to restore the differential backups until you find the backups taken by this utility.
Jeff,
A few things:
1) Thank you for your help. I stayed up to midnight, the time which the maintenance plan executes, ran an exec sp_who2 and found another server trying to backup the same database that I was backing up. I've already sent off the appropriate angry DBA emails to see who scheduled a database backup process without letting me know.
2) I am not understanding how I am breaking the log chain. Why would the differential backup be unusable. If I had a problem right after the maintenance plan ran, I would restore the full backup from the maintenance plan. If that wasn't available then it would be full backup before maintenance plan and then the differential backup. Isn't that correct?
3) If you had inherited the above scenario (as I d did), what would you do to improve the process? I know we are speaking in general terms. To properly optimize a maintenance you have to know the system, the processes and the goal of the plan. But generally, what would you do to make better process since you didn't like the current one that I stated?
Thanks again.
Tony
Things will work out. Get back up, change some parameters and recode.
May 7, 2011 at 4:46 pm
WebTechie38 (5/7/2011)
2) I am not understanding how I am breaking the log chain.
The switch to simple recovery breaks the log chain. Means that log backups taken after the switch back to full cannot be applied to a backup from before.
Why would the differential backup be unusable.
Backup of a recently corrupt database where the corruption is discovered a day later and the diff contains the corrution.
Damage to the diff backup that makes it impossible to restore, but not severe enough to cause the backup to fail.
Even if the diff is intact, if the backup taken after the switch back to full is not restorable, all the log backups after that are useless and if something goes wrong sometime after the best you'll be able to restore is to that diff backup.
Personally, if that were mine, I'd switch to bulk-logged recovery, that way the index rebuilds are minimally logged, but the log chain remains intact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2011 at 6:56 pm
Tony,
Gail stated exactly what I would have stated - but better. 🙂
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2011 at 9:29 pm
Jeffrey Williams-493691 (5/7/2011)
Tony,Gail stated exactly what I would have stated - but better. 🙂
+1 She does have a way with words. 🙂
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 8, 2011 at 12:52 am
Jeff / Gail,
Thanks for the lesson. I wanted to get your impression of what I could do better. I will look into the bulk logged recovery. To be honest, I've never used bulk logged recovery as long as I have been using SQL Server. I just switch between Full and Simple.
Thanks again for the pointers.
Tony
Things will work out. Get back up, change some parameters and recode.
May 8, 2011 at 1:33 am
HI,
I also tried the bulk-logged recovery. But it increases the log file size and reduce the performance of re-indexing.
Really there is a risk. If server down after re-index and before backup , we can not user the pervious backup and need to restore the Database to last full backup and last differential backup before the re-indexing.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply