DBCC checkdb encounters a database snapshot could not being created

Source: Internet
Author: User
Tags filegroup

When you back up a customer's database (the database version is SQL 2005 Express version), the DBCC CHECKDB encountered the following error message:

DBCC CHECKDB (' db_name ');

MSG 5030, Level 16, State 12, line 1th

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

Msg 7926, Level 16, State 1, line 1th

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

There are typically two reasons for creating a database snapshot failure:

1: The database has a read-only filegroup.

2: File system without support for sparse files (Parse file)

The English text is as follows

No Parse file support by the file system.

A. Parse file is not supported in FAT32 check the file system of the datafiles. If FAT32 use DBCC CheckDB with Tablock Option

B. To get the volume information of the file system in which we had the datafiles we use! GetVolumeInformation API.

This API would fail if SQL Server startup account does not has full permission on Volume in which the data file is located.

Grant full permission for the startup account of SQL Server on the root volume of the datafies.

I checked the database found not set as read-only filegroup, this PC is Windows Xp, file system system is really FAT32, using DBCC CHECKDB (' db_name ') with TABLOCK still error, in fact, when the file system is FAT32, DBCC CHECKDB can only be successful in single-user mode. So I encountered this error when I was doing DBCC CHECKDB in the database, so I tested it in the test environment as follows:

To test the DBCC CHECKDB problem under the FAT32 file system:

1: The file for the newly created database test is located on the FAT32 disk. If no session accesses the database test (equivalent to single-user mode), DBCC CHECKDB succeeds if the test is opened in a new window, and then another window executes DBCC CHECKDB (' TEST '), the following error is reported, in addition DBCC CHECKDB (' Db_name ') with TABLOCK also needs to be successful in single-user mode.

References:

http://blogs.msdn.com/b/karthick_pk/archive/2010/03/07/ Dbcc-checkdb-fails-the-database-could-not-be-checked-as-a-database-snapshot-could-not-be-created-and-the-database-or-tabl E-could-not-be-locked.aspx

https://support.microsoft.com/en-us/kb/928518

Https://ask.sqlservercentral.com/questions/51856/dbcc-checkdb-with-fat32-file-system.html

DBCC checkdb encounters a database snapshot could not being created

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.