Today I ran consistency check on a database and it throw below error:
DBCC checkdb on msdb database failed with errors:- Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
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.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 1117(failed to retrieve text for this error. Reason: 1815) to SQL Server during a read at offset 0000000000000000 in file 'c:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC7'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
DBCC checkdb on msdb database failed with errors:- Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
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.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 1117(failed to retrieve text for this error. Reason: 1815) to SQL Server during a read at offset 0000000000000000 in file 'c:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC7'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
In most cases this error be caused by a permissions issue on the root of the drive where the database belongs. I tried running CheckDB query on some other databases located at the same location. and I had not got any error on other databases. So it is confirmed that the error is not related to permissions.
The corruption is so bad that I could not run CheckDB on that database. Than I restored the database from last good backup.
DBCC CHECKDB command is the best solution to repair corrupt SQL Server database or you can also restore your database from updated backup. There is one another solution to repair corrupt SQL Server database is use of third party SQL Server database recovery Software. I used SQL Recovery Software offered by Stellar Phoenix.
ReplyDeletetry this
ReplyDeleteALTER DATABASE saveplus SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('saveplus', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE saveplus SET MULTI_USER
hope its help
check the check TempDB if you still encounter error.
ReplyDelete