dbcc checkdb failed for system database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

dbcc checkdb failed for system database

Author

Topic

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-02-16 : 04:55:18

Hi ,

We have a cheduled job to perform DBCC CHECKDB on system databases
but it been failing with the following error:

[1] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5030: [Microsoft][ODBC SQL Server Driver][SQL Server]
The database could not be exclusively locked to perform the operation.
[Microsoft][ODBC SQL Server Driver][SQL Server]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.
The following errors were found: [Microsoft][ODBC SQL Server Driver][SQL Server]
The database could not be exclusively locked to perform the operation. [Microsof...
Process Exit Code 1. The step failed.

The same job also has been setup on other server and are running fine.

So I do not understand why this job failed on this server

Appreciate if someone can help on this

Thank you

Regards
Zuryati

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 05:45:31

As I read the message the database was locked (by another user/process) at the time and thus the operation could not proceed.

However, that does seem rather odd!!

I suggest you run it manually (i.e. in SSMS) and see if you get a different message.

DBCC CHECKDB('msdb') WITH NO_INFOMSGS

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-16 : 07:28:35

No, that's not what's wrong. MSDB will always have an active connection when SQL Agent is running.

CheckDB by default takes a DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to take an exclusive database lock to ensure that no one's moving stuff around while it's checking. The root problem is not that the lock could not be obtained, it's that the snapshot could not be created.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-16 : 07:31:15

Zuryati, has the maint plan ever succeeded on this server?

If so, are there any errors around the time that it last ran successfully? It can happen sometimes that the snapshot doesn't get dropped properly and future runs fail.

If it's possible, can you restart the SQL instance (just the SQL service, not the entire server) and run checkDB again, see if it succeeds or fails. Also check and see if the windows event log has any futher messages.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 07:55:13

" MSDB will always have an active connection when SQL Agent is running."

Indeed, I was meaning that some user/process was blocking DBCC from running - didn't express it very well though!

I couldn't think of what might cause that, but your suggestion of snapshot not being successfully dropped is something I had not heard of before; useful KB

thanks.

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-02-16 : 12:22:01

Hi Kristen, Gail

Thanks for the reply.

This maintenance was run successfully except for the past 2 weeks.

For the suggestion to restart the sql services, I need to find the downtime for this since this is a production server and we are running 24x7

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:24:56

Hmmm ... I wonder if at some point your backups may fail if SQL is unable to access MSDB (if the the corruptions in MSDB become an issue) - i.e. SQL will want to store details of each backup it makes, in MSDB - presumably AFTER it completes the backup ... but maybe not.

Also, the Schedules for Backup etc. will be in MSDB I think? so other things may be at risk.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-16 : 16:45:20

Why would SQL be unable to access MSDB? CheckDB couldn't get an exclusive lock, because there were other connections (specifically SQL Agent), but that won't stop anything else accessing the DB. Most connections only want shared database locks. Only things that need exclusive DB locks are some ALTER DATABASE statements and CheckDB when run with the TabLock option (which is not default). CheckDB being unable to create a DB snapshot doesn't mean that the DB is corrupt.

Zaty, can you check back to when the maint first started failing, see if there are any odd messages in the SQL error log, anything mentioning snapshots?

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-02-16 : 17:26:06

Hi Kristen, Gail,
Thanks for the reply

I tried manually run dbcc command from ssms for databases master & msdb but both encountered same error:

DBCC CHECKDB('msdb') WITH NO_INFOMSGS
DBCC CHECKDB('master') WITH NO_INFOMSGS

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.

But the above error was not get written to the sql server log.

The last good run was on Feb 3rd . There was no error message found on the sql log and window log since then.

On Feb 4th , this is recorded on Event Viewer
Product: MSXML 6 Service Pack 2 (KB954459) -- Installation completed successfully.

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 02:57:55

" Why would SQL be unable to access MSDB?"

Sorry. I answer too many questions and they get mixed up in my head! I was assuming there was trouble in MSDB - but you are absolutely right, until DBCC CHECKDB actually gets the chance to run AND reports some corruptions then there is nothing to worry about on that score

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-02-17 : 09:56:11

http://www.sqlskills.com/BLOGS/PAUL/post/Issues-around-DBCC-CHECKDB-and-the-use-of-hidden-database-snapshots.aspx

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-17 : 10:57:45

I forgot you had that blog post. Sorry Paul.

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-02-18 : 17:41:58

Hi all,

I read the link by Paul but I still have no idea how to resolve my current issue.

Appreciate your help

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 01:30:39

Create your own database snapshot of MSDB and run DBCC CHECKDB on that ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-19 : 03:24:32

quote:
Originally posted by zaty2405

I read the link by Paul but I still have no idea how to resolve my current issue.

From the blog post:

quote:
The first issue is around the permissions of the SQL Server service account. To be able to create the NTFS alternate streams, the service account must have the privileges to create files in the DATA directory of the SQL Server instance. This is a really difficult problem to track down as the actual NTFS failure message is not surfaced by the snapshot creation code.

Check the permissions, make sure that no one's changed them and that the SQL service account has full control of the directory the data files are in.

quote:
The second issue is around the use of HP PolyServe. Upgrading to Matrix Server 3.6.1 disables support for alternate streams in the filesystem, effectively breaking DBCC CHECKDB.

Are you using HP Polyserve. If so, check the version of Matrix Server.

Also, have you had a chance to restart SQL?

One other thing. How much free space is there on the drive where the MSDB files are?

--
Gail Shaw
SQL Server MVP

gefleborg
Starting Member

1 Post

Posted - 2010-03-26 : 08:48:58

I think the solution is that the sql server service account must have at least the "list right" from the root and down on the disk where master and msdb resides.

What I don't get is why I can't get a proper error message when I do an object audit on my drive.

/Peter

quote:
Originally posted by zaty2405

Hi all,

I read the link by Paul but I still have no idea how to resolve my current issue.

Appreciate your help

Thanks

ajeet_rai
Starting Member

1 Post

Posted - 2012-03-27 : 22:25:18

Hello Zuryati,

Please check the access permission for drive which have this jab send the out put. This problem occurred due to not enough permission to user, who perform this action. check the other drive users and grant all the users in job completion drive.

Thanks
Ajeet Rai

chandana
Starting Member

1 Post

Posted - 2012-08-28 : 23:17:25

it was due to the service account not having permission to the Data Folder . I gave the permission and reran the job successfully.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources