Checkdb giving error for master database

Posted on

Question :

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).

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)

  1. Ensure all services that connect to SQL Server are off/stoped (this includes services like SSIS, SQL VSS writer, etc)
  2. Restart SQL Server in single user mode using the -m startup parameter (described here)
  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *