SQL 2008 - DBCHECK Failed

Question

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?

Answer 1

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.

Answer 2

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/