Scheduling DBCC Checkdb on SQL 2005 standard

Question

Post reply

Scheduling DBCC Checkdb on SQL 2005 standard

Josh Leane-155117

SSC-Addicted

Points: 488
More actions
May 21, 2009 at 10:44 pm

#134437

Hi
Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
When using a maintainence plan for the Check Database Integrity task to schedue dbcc checkdb, fails with error
Failed: (-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "The database could not be exclusively locked to perform the operation.
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
http://support.microsoft.com/kb/928518 says this is to be expected if database snapshot cannot be created or database cannot be locked.
So my qu is given SQL 2005 Standard does not support database snapshots, and db locks can't be granted on most databases, esp if supporting apps like Sharepoint, does this mean that you can't run an dbcc checkdb without shutting down all the apps first, so therefore you can't schedule one? Which seems surprising to me as it wasn't like this in SQL 200 standard. And the worksround in the MS article will only work for Enterprise. Or am I missing something?
Thanks for any help, Josh

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As far as I know, the hidden snapshots that CheckDB uses work on every version of SQL right down to express.

Are there any errors in the error log?

If you run checkDB manually, what's the exact errors that you get?

DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 2

CHECKDB uses its own internal snapshot when the operation is in process.

From BOL

Internal Database Snapshot

DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed. For more information, see Understanding Sparse File Sizes in Database Snapshots and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL). If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks.

DBCC CHECKDB fails when run against master if an internal database snapshot cannot be created.

Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Which database are you trying to run CHECKDB against when you get the failure? There are more errors which explain why the snapshot could not be created - can you post these too?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Josh Leane-155117 SSC-Addicted Points: 488 More actions · Answer 4

Hi, thanks for the replies, appreciate it

I was doing this origanially on a dev server. I ran

DBCC CHECKDB (adventureworks) WITH NO_INFOMSGS, ALL_ERRORMSGS

and get

Command(s) completed successfully.

Then start a new query window, execute

use adventureworks

go

and then run the original dbcc checkdb and get the error I originally posted. There aren't any other errors either in the SQL or Windows logs

Then restored the adventureworks to production server, identical versions of SQL & Windows, ran the same sequence and it worked fine. So it seems as though what your suggesting is right, that it does create the shapshot in the background even in Standard version, but this is failing for some reason on the dev server and with the database being accessed, the dbcc fails as it can't get an X lock

The only real difference is hardware, the production is on a regular HP proliant server, the dev is on VMWare ESX server, but the OS & SQL is setup identically

I would be interested in trobuleshooting the issue with the dev server, any ideas how?

Thanks, Josh

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Josh Leane (5/26/2009)
The only real difference is hardware, the production is on a regular HP proliant server, the dev is on VMWare ESX server, but the OS & SQL is setup identically

I'm not trying to minimize this difference here, but I'd be really surprised to learn that your issue has to do with being on ESX. I have most of my production servers on ESX and CHECKDB runs fine there, so I'd be surprised if it really boiled down to ESX as the issue. That doesn't mean that you aren't having a problem resource wise that is preventing the snapshot from succeeding.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

ok - it's nothing to do with your hardware. And yes, it uses a snapshot in all versions of SQL Server.

I wrote DBCC CHECKDB for 2005 - if you post all the error messages as I asked previously I can tell you exactly what's going on.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Josh Leane-155117 SSC-Addicted Points: 488 More actions · Answer 7

Hi Paul

The output from running DBCC CHECKDB (adventureworks) WITH ALL_ERRORMSGS is

Msg 5030, Level 16, State 12, Line 1

The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 1

Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

There aren't any errors in the SQL or Windows logs (I thought that was what you were originally asking for)

Thanks for your help,

Josh

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Hmmm. Are any of the following true:

- you're running on a file system apart from NTFS

- you have zero free space on the drive hosting the database

I can't think of anything else that would prevent the snapshot being created. What SP + build number of SQL Server are you using?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Josh Leane-155117 SSC-Addicted Points: 488 More actions · Answer 9

Hi Paul

Definitely all drives are NTFS, and plenty of Gb free space on all drives.

I'm running SP2 build 9.00.3077.00

Thanks, Josh

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

Do you have any read-only files or filegroups in the database? That would stop it.

Also, did a previous CHECKDB get killed for any reason? There could be a hidden database snapshot lying around, which would prevent it creating another one. If you select * freom sys.databases, is there an unusual db name including AdventureWorks?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Josh Leane-155117 SSC-Addicted Points: 488 More actions · Answer 11

Hi,

no, there aren't any read-only filegroups and the result from select * from sys.databases just lists the dbs that should be present

The dbcc not being able to create a shapshot happens for all databases on the server. If I stop the front end apps, then the dbcc fails to create a snapshto but runs ok via a X lock

eg dbcc checkdb(esystems) gives

DBCC results for 'eSystems'.

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

DBCC results for 'sys.sysrowsetcolumns'.

There are 758 rows in 8 pages for object "sys.sysrowsetcolumns".

etc ...

Thanks, Josh

Paul Randal One Orange Chip Points: 29544 More actions · Answer 12

Hmm - weird. How long are the pathnames to the database files?

Could be a privilege issue. Does the SQL Server service account have privileges to create files in the data directory?

If neither of these are the case, then I'm out of ideas without attaching a debugged to your server.

In the meantime, you can create your own database snapshot and check that - exactly the same as what CHECKDB's doing under the covers (or not, in this case)

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Josh Leane-155117 SSC-Addicted Points: 488 More actions · Answer 13

Hi,

It was permissions for the service account. The data files are installed to E:\MSSQL.1\MSSQL\Data and the SQLServer2005MSSQLUser$ group had full control over the Data folder, so could create DBS, backup etc, but the group didn't have any permssion on the E: root drive. Once I gave it read & execute to E:\ it worked fine. The snaphots is the only functionality that I've noticed that was effected

Thanks for all your help with this

Thanks, Josh

Paul Randal One Orange Chip Points: 29544 More actions · Answer 14

Excellent! Another problem bites the dust.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005