Sql-server – Checkdb giving error for master database

dbcc-checkdbsql serversql-server-2008-r2

When i run dbcc checkdb on master database i get error like

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.

When i searched for it i got lot of links stating that it happens when database has read-only file group or file is in FAT32 file system. But when i checked the master database was not in readonly file group. But mdf file was in FAT32 file system. So solution was to use TABLOCK with checkdb command. So i tried dbcc checkdb With Tablock . But i am hetting same error message. So what is the reason for this error. I am using sql server 2008 R2 express(service pack 2).

Best Answer

The FAT32 is the reason here. And you can't do a TABLOCK CHECKDB on the Master database. WITH TABLOCK puts the database in read only mode - you can't do that to the Master database because of system operations that required to run and run on the Master database.

You can verify this easily enough by creating a new user Database on the same file system and then trying a CHECKDB, seeing the same error and then doing a CHECKDB on that on with the TABLOCK option. My guess is you get the same error without the option and you do not get the error when specifying the option (as long as you don't have a connection open to the database by accident)

Unfortunately you are sort of stuck from being able to do regular CHECKDBs on Master until you resolve this.

In the meantime you can do this process ( a bit of a pain but it works)

Ensure all services that connect to SQL Server are off/stoped (this includes services like SSIS, SQL VSS writer, etc)
Restart SQL Server in single user mode using the -m startup parameter (described here)
Connect as the single user and then do your CHECKDB with the TABLOCK specified.

I just tried this on my SQL Server 2012 instance and it worked. Only way you can do this.

To me the big question here is - why is your file system FAT32? What OS is this on? Are all of your databases on FAT32? It is possible to be on FAT32, but the recommendation is to be on NTFS volumes. On FAT32 you don't get certain permission benefits that you get on NTFS - leaving some files open for manipulation and resulting problems, and obviously this CHECKDB issue.

Related Solutions

Sql-server – CHECKDB fails on MSDB and Master system databases

There is an internal snapshot that SQL Server will try to create when you run certain DBCC commands. There is really good information about this on the BOL reference for DBCC.

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.

The master database is particularly interesting with this, as if the internal snapshot can't be created then DBCC CHECKDB will fail against master. As per the above reference:

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

So, what can cause the internal snapshot creation to fail? The above reference has a nice list:

An internal database snapshot is not created when a DBCC command is executed:

Against master, and the instance of SQL Server is running in single-user mode.

Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.

Against a read-only database.

Against a database that has been set in emergency mode by using the ALTER DATABASE statement.

Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.

Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:

A read-only filegroup

An FAT file system

A volume that does not support 'named streams'

A volume that does not support 'alternate streams'

My recommendation is to look at that information and find which one is applying to your situation. That should answer your question and move towards the resolution.

Sql-server – DBCC CHECKDB errors

When a Operating System error is encountered during the IO request, this will be reported as a 823 error. 823 error messages are reported only for failed OS IO operation errors. Given that, you need to check your system event log for any disk or file system related entries. You will need to fix the disk or file system problems or else the corruption will return.

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.

This error is generally thrown when we have corrupt or inconsistent metadata.

• The actual error would be: Database %d has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup

• The metadata inconsistency error is in most cases irreparable Before any attempts to fix the corruption or recover the data, copy the mdf and ldf files off to a safe location. That way you can at least always return to your current state should your recovery attempts further damage the database.

You have a few options to try: 1. Restore from backup. 2. Manually extract as much data as you can, create a new table, import the data into the new table, delete corrupted table, rename new table to corrupted table name. 3. Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. <--this probably won't work 4. Purchase expensive 3rd party application to recover the data.

Only the restore from backup is guaranteed to work, as long as you haven't been backing up a corrupt database.

Best Answer

Related Solutions

Sql-server – CHECKDB fails on MSDB and Master system databases

Sql-server – DBCC CHECKDB errors

Related Question