March 5, 2012 at 7:44 am
Although it appears to work fine, and all the critical steps work, my backup plan fails every day. Can anyone help me debug this? (SS2k5, SSMS2k8)
I attached an image of the errors, etc. but don't know how to display it.
Jim
March 5, 2012 at 7:54 am
Jim
It appears it's the Check Database Integrity task that's failing. Can you show us the error message for that, please?
John
March 5, 2012 at 11:26 am
Forgive me, I had intended to show that, but it got covered by another window. Here it is.
Jim
March 5, 2012 at 2:43 pm
check the maintenance plan history and the error logs around the failure time for more info and post back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2012 at 2:44 pm
That's a pretty important task to have completing. Can you go into the check database integrity task in the maintenance plan, click the View T-SQL button and post what you find?
It looks like you might also be running into this, http://connect.microsoft.com/SQLServer/feedback/details/126163/maintenace-plan-integrity-checks-fail-with-alter-failed-for-server-xxx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2012 at 3:11 pm
That issue is actually very simple to fix - somebody has run the following:
sp_configure 'Allow Updates', 1;
This caused the integrity checks to fail. Execute this:
sp_configure 'Allow Updates', 0;
And it should take care of the issue.
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
March 6, 2012 at 8:47 am
I ran the "sp_configure 'Allow Updates', 0;" statement. Result was "...changed from 0 to 0. Run reconfigure..."
Don't know what 'reconfigure' is, but it appears Allow Updates was already zero....
Maint plan step T-SQL image attached.
Jim
March 6, 2012 at 8:58 am
Can you try running one of the DBCC CHECKDB commands listed in the T-SQL window by itself and see if you get a better error message?
You can also run this statement to get details:
USE msdb ;
GO
SELECT
SP.name,
SP.create_date,
SP.owner,
SS.subplan_name,
SS.subplan_description,
SL.start_time,
SL.end_time,
SL.succeeded,
SL2.command,
SL2.error_number,
SL2.error_message,
SL2.start_time
FROM
dbo.sysmaintplan_plans AS SP
JOIN dbo.sysmaintplan_subplans AS SS
ON SP.id = SS.plan_id
JOIN dbo.sysmaintplan_log AS SL
ON SS.plan_id = SL.plan_id AND
SS.subplan_id = SL.subplan_id
JOIN dbo.sysmaintplan_logdetail AS SL2
ON SL.task_detail_id = SL2.task_detail_id
WHERE
SP.name = N'Your Plan Name Here' AND
SS.subplan_name = N'Your Sub Plan Name HERE' AND
SL.start_time >= DATEADD(DAY, -1, CURRENT_TIMESTAMP)
ORDER BY
SL.start_time DESC,
SL2.start_time DESC
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2012 at 2:09 pm
Jack Corbett (3/6/2012)
Can you try running one of the DBCC CHECKDB commands listed in the T-SQL window by itself and see if you get a better error message?You can also run this statement to get details:
USE msdb ;
GO
SELECT
SP.name,
SP.create_date,
SP.owner,
SS.subplan_name,
SS.subplan_description,
SL.start_time,
SL.end_time,
SL.succeeded,
SL2.command,
SL2.error_number,
SL2.error_message,
SL2.start_time
FROM
dbo.sysmaintplan_plans AS SP
JOIN dbo.sysmaintplan_subplans AS SS
ON SP.id = SS.plan_id
JOIN dbo.sysmaintplan_log AS SL
ON SS.plan_id = SL.plan_id AND
SS.subplan_id = SL.subplan_id
JOIN dbo.sysmaintplan_logdetail AS SL2
ON SL.task_detail_id = SL2.task_detail_id
WHERE
SP.name = N'Your Plan Name Here' AND
SS.subplan_name = N'Your Sub Plan Name HERE' AND
SL.start_time >= DATEADD(DAY, -1, CURRENT_TIMESTAMP)
ORDER BY
SL.start_time DESC,
SL2.start_time DESC
Using SSMS, I ran the T-SQL generated by the step. Worked fine, no error messages.
Using SSMS, I ran the T-SQL query above, it gave me pretty much the same messages I posted earlier, but it is easier to read. I'll keep it for future reference.
Could this be some sort of privs error? I have certain privs running SSMS, but Agent does not?
Jim
March 6, 2012 at 2:59 pm
Shouldn't be privileges as the SQL Agent Service Account is granted sysadmin rights on the SQL Server. You'd only have permissions issues if you were trying to access something on the network.
It may be that something earlier in the maintenance plan is running the sp_configure statement that was mentioned earlier as it can cause this problem.
You can check the T-SQL for each step of the plan to see if it has that statement anywhere.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2012 at 4:32 pm
You need to run the command with the option to reconfigure - or the changes will not be effective until you restart SQL Server.
Execute sp_configure 'Allow Updates', 0; reconfigure with override;
Also, review this thread as there are additional issues that could cause this problem:
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/54b6b68c-c879-4e99-bfcf-7f88bda7efb5
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
March 7, 2012 at 7:33 am
I fixed the one error on operator notification. That was easy, apparently I had no default notify path or something.
I moved the CheckDB step to its own "plan", and it worked fine. You're probably right that some other step changed that parameter. Once I took it out of the sequence, it worked fine. I'll just leave it out of the sequence.
Thanks for your help, folks!
Jim
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply