Author: AJ Mendo

AJ has worked with databases for more than 12 years. AJ specializes in database performance analysis, tuning, high availability, and disaster recovery. AJ has been known to present at SQL Server user groups, SQL Saturday’s and online. AJ is on the board of POSSE and the media manager for CACTUSS. He gives his time freely for SQL Events and loves being a part of the SQL Community.

Msg 5030, Level 16, State 12, Line 1

I like to be preventive or proactive in SQL server maintenance (or PM for short). As part of the PM I have in place I run database integrity checks weekly. If there are issues when running the check(s) I get an email with enough details such as database name and server to make things a little easier when investigating further. I ran into an error the other day I wanted to share what I found and how I fixed it.

Problem

This is an example of the email I received this morning:

Daily DBCC CHECKDB Error: TestDB on server Server_Name 

My initial reaction was “Oh, no!” (I actually used a few other more colorful adjectives I won’t repeat)

The first thing I did was to copy the backup from last night to a test server and ran CHECKDB to see what I could see and make sure we did not have any corruption. After running the check and finding no issues I started to investigate further. I know running CHECKDB can be resource intensive (one reason, if you can, to off load that maintenance task to a “non-production” server). I wanted to make sure there weren’t any jobs running at the same time. Finding none, I wanted to see what the actual error was. Not finding anything related to the error in the logs I opened a query window and ran the following command and received the following results.

Msg5030-Level16-State12-Line1

Now I think it is important to note here the server in question is still being configured/tested before deploying to production so I was able to run CHECKDB on the server directly (and since I confirmed no issues were present already).

Solution

It appears there is a bug for this particular issue. Bug #: 489609 (SQLBUDT)

It seems if you get this error you have potentially one of two problems.

  • At least one other connection is using the database against which you run the DBCC CHECK command.
  • The database contains at least one file group that is marked as read-only.

I was fairly sure the database did not have a “read-only” file group but I checked just to make sure because this project has too many cooks in the kitchen (too many people with sysadmin access). I confirmed no read-only file groups were present.

sp_helpfile_info

I know there are other connections (several in fact) to this database. So we found out what caused the error. Now, how do we fix it?

We can either off load this task to another server. If we do that, we will need to build out a process of either copying a backup to another server or we can back up the database to a different server, restore and run CHECKDB.

Another option is to create a snapshot. Run CHECKDB against the snapshot we create and then drop the snapshot when finished.

Before_DBSnapShots

The code below is an example of how to go about doing this.

createsnapshot

After_DBSnapShots

runCheck

CHECKDB_results

dropSnapshot

Conclusion

No matter how much preventive maintenance you do on your servers/databases corruption will happen at some point. Running integrity checks is one way to help combat corruption and be aware of it as close to the time it occurred as possible. The sooner you know the more options you will have to remedy the issue. To read more about this error and how to work around it see this article. http://support.microsoft.com/kb/928518

Cheers!