Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup.

This has been working fine for months. It has started failing. If I run a DBCHECK on the Databases, they are pass fine. I even went into the maintenance plan and clicked 'View SQL' to get the TSQL that is run during the Plan and it again all passed:

USE [ClientPortal]
GO
DBCC CHECKDB(N'ClientPortal')  WITH NO_INFOMSGS
GO
USE [PHJ_Common_Objects]
GO
DBCC CHECKDB(N'PHJ_Common_Objects')  WITH NO_INFOMSGS
GO
USE [PilotWebApp]
GO
DBCC CHECKDB(N'PilotWebApp')  WITH NO_INFOMSGSenter code here

Why would this fail during the plan but work fine when run manually?

share|improve this question
add comment

migrated from stackoverflow.com Jan 22 '12 at 6:55

This question came from our site for professional and enthusiast programmers.

2 Answers

The maintenance plan is usually executed with another user account. Check the properties of SQL Agent. If the Connection tab specifies Use Windows Authentication, it runs under the same user as the SQL Server Agent service. You can see that in the Local Services control panel, under the Log On tab.

Once you figure out the user account, try to run the script as that user.

share|improve this answer
add comment

Going forward, if you have the time, I'd suggest implementing a stored procedure/job approach to maintenance such as CheckDB. They are much more robust and scalable than maintenance plans, in my opinion.

Ola Hallengren offers excellent maintenance scripts, which are easy to deploy: http://ola.hallengren.com/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.