Getting error on maintenance plan
-
I used the wizard to create a maintenance plan. I was about to put in the cleanup part but I wanted the subfolder to be there, so I test ran the plan in jobs under sql agent. It failed with the following:
07/22/2008 15:27:44,MaintenancePlan - PortalMessengerTest,Error,1,ASQLM2,MaintenancePlan - PortalMessengerTest,Subplan,,Executed as user: ASQLM2\SYSTEM. ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:27:44 PM Progress: 2008-07-22 15:27:45.13 Source: {FD08DDDB-59D6-4F7C-AC4A-15A2E16503E8} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-07-22 15:27:45.48 Source: Check Database Integrity Executing query "USE [PortalMessengerTest] ".: 100% complete End Progress Progress: 2008-07-22 15:27:46.45 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2008-07-22 15:27:47.21 Source: Rebuild Index Executing query "USE [PortalMessengerTest] ".: 8% complete End Progress Error: 2008-07-22 15:27:47.23 Code: 0xC002F210 Source: Rebuild Index Execute SQL Task Description: Executing the query "ALTER INDEX [PK_GroupAssignment_GroupName/OwnerUse... The package execution fa... The step failed.,00:00:05,0,0,,,,0
On top of that, I setup three different emails to be sent on a failure, completion, and success. I didn't receive any of the emails. Please note that the emailing is something I just set up. It tests ok, but haven't had it actually fire off from a job.
Now...looking at that error, is it failing to rebuild the index because of some rights issue? And wouldn't it still send an email if it errored out?
-
think I might of found it. Can online reindexing only be done on enterprise editions? if so, why even have the option on standard?
-
It's an Enterprise Edition feature. The reason the option exists is because you use the same Management Studio for all versions, even for multiple servers with different versions, at the same time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Ok. Still getting errors. Is it common to make one large maintenance plan for instances or do most people just make little maintenance plans for each action? Or do people normally just make jobs without worrying about maintenance plans? I understand that different databases require different attentions, but was trying to look for a good one to manage at least the system databases for both standard and enterprise editions. How do most people handle maintenance on their databases both system and user?
Also, if you build a maintenance plan with multiple tasks, if one errors out, do they all stop or just the ones that are connected with a workflow (ie...arrows)? Can you build it so they all fire off and if one errors, that is the only one that fails? I am just wondering if I am going to have to build tons of little maintenance plans for each task.
One last thing 🙂
I have Microsoft SQL Server 2005 - 9.00.3042.00. That should mean that I have the subfolder checkbox in the cleanup task when I am building my maintenance plans but it isn't there. Any ideas?
-
My usual practice is to break up maintenance plans a bit. I'll rebuild some indexes one night, other indexes the next night, etc., and schedule that to repeat weekly/monthly.
Takes a bit more to set up, but gives me more control over it. And once it's set up, it pretty much just goes on its own.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (7/23/2008)
My usual practice is to break up maintenance plans a bit. I'll rebuild some indexes one night, other indexes the next night, etc., and schedule that to repeat weekly/monthly.Takes a bit more to set up, but gives me more control over it. And once it's set up, it pretty much just goes on its own.
Do you send yourself email notifications? Wouldn't that be a lot of emails if you have 20 instances with maybe 30 or 40 databases?
On a second note, does this fire off an email if not all of the above succeed? What if only two succeed and I get the email, I would never get notified about the failure. Or would I get both emails?
-
I have SQL Server Agent send e-mails based on whether the job succeeded or failed. I have them go into different folders in Outlook based on the server and the success/failure. I also have it mark the "success" ones as read. That way, with one look at the folders in Outlook, I can see if any of the failure folders have unread e-mail, and immediately know which server. Then I just open the e-mail and get the job. It's pretty simple.
I could make SQL Server do more of that work for me, but Outlook was easier to set up. Defining rules for e-mails is just too easy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
jason.stephens (7/23/2008)
GSquared (7/23/2008)
My usual practice is to break up maintenance plans a bit. I'll rebuild some indexes one night, other indexes the next night, etc., and schedule that to repeat weekly/monthly.Takes a bit more to set up, but gives me more control over it. And once it's set up, it pretty much just goes on its own.
Do you send yourself email notifications? Wouldn't that be a lot of emails if you have 20 instances with maybe 30 or 40 databases?
On a second note, does this fire off an email if not all of the above succeed? What if only two succeed and I get the email, I would never get notified about the failure. Or would I get both emails?
The way you have this setup, you will only get notified if all cleanup tasks complete successfully, or all cleanup tasks fail. Based up this, you really only need one notify operator task and you want to change the precedence to completion. That way, you will get a notification when all tasks complete.
If you only want the notification when one (or more) of the tasks fail - you need to change the precedence from AND to OR (it will change to a dotted line, instead of a solid line).
Now, I have to ask - why do you need 3 separate tasks to cleanup backup files when you have one task to backup?
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 -
Jeffrey Williams (7/23/2008)
jason.stephens (7/23/2008)
GSquared (7/23/2008)
My usual practice is to break up maintenance plans a bit. I'll rebuild some indexes one night, other indexes the next night, etc., and schedule that to repeat weekly/monthly.Takes a bit more to set up, but gives me more control over it. And once it's set up, it pretty much just goes on its own.
Do you send yourself email notifications? Wouldn't that be a lot of emails if you have 20 instances with maybe 30 or 40 databases?
On a second note, does this fire off an email if not all of the above succeed? What if only two succeed and I get the email, I would never get notified about the failure. Or would I get both emails?
The way you have this setup, you will only get notified if all cleanup tasks complete successfully, or all cleanup tasks fail. Based up this, you really only need one notify operator task and you want to change the precedence to completion. That way, you will get a notification when all tasks complete.
If you only want the notification when one (or more) of the tasks fail - you need to change the precedence from AND to OR (it will change to a dotted line, instead of a solid line).
Now, I have to ask - why do you need 3 separate tasks to cleanup backup files when you have one task to backup?
Thanks.
For some reason, I don't have the checkbox to check subfolders for some reason. I posted up above asking why that may be since I have SP2 installed. I read that they put the checkbox in SP1. I have to have each subfolder specifically pointed to for the cleanup.
-
Verify the version on your client system. I am betting that your client software has not been upgraded to the correct version.
Both your server and the client need to be on at least: 9.0.3054. If you are at 9.0.3042 - you have missed a very important patch that will cause lots of problems with maintenance plans.
Ideally, if possible - you really should be upgraded to CU6/7. I know some applications will not work on those releases, so make sure applying those does not break anything.
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 -
Jeffrey Williams (7/23/2008)
Verify the version on your client system. I am betting that your client software has not been upgraded to the correct version.Both your server and the client need to be on at least: 9.0.3054. If you are at 9.0.3042 - you have missed a very important patch that will cause lots of problems with maintenance plans.
Ideally, if possible - you really should be upgraded to CU6/7. I know some applications will not work on those releases, so make sure applying those does not break anything.
Thanks so very much. I remoted to the server and the checkbox was there. Someone needs to change my title from Grasshopper to Tard 🙂 I forgot to apply the SP1 and 2 to my workstation 🙂
-
Can you believe it? I have another quick question 🙂
When I use that checkbox to go into subfolders, does it know to only delete old files from the databases mentioned earlier in the maintenance plan or is it going to delete files from all of the subfolders? The reason I was curious is because I was going to create a plan for the system databases and then one for each user database. Don't want every plan to purge old files out of every subfolder.
From my testing, it does. How can I limit this? Do I have to use a T-SQL block instead of the Cleanup block?
-
It will delete all backup files that meet the requirements in the subfolders. So, if you have it setup to delete .bak files older than 1 day - that is exactly what it will do.
I would recommend creating a specific folder for each set of backups. For example:
x:\SQL Backups\System Databasesx:\SQL Backups\User Databases
For the system maintenance plan - do not have the system create a folder for each database. In the maintenance plan cleanup task, do not check the box for subfolders.
For the user databases - create a subfolder for each database and in the maintenance cleanup task check the box. Don't forget to add a cleanup task for your transaction log backups, or they will not get removed.
What you will end up with is:
x:\SQL Backups\System Databases Files: master_backup_yyyymmddhhmmss.bak
model_backup_yyyymmddhhmmss.bak
msdb_backup_yyyymmddhhmmss.bak
x:\SQL Backups\User Databases\Database1
Files: database1_backup_yyyymmddhhmmss.bak
database1_backup_yyyymmddhhmmss.trn
...
x:\SQL Backups\User Databases\Database2
Files: database2_backup_yyyymmddhhmmss.bak
database2_backup_yyyymmddhhmmss.trn
...
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply