September 6, 2016 at 2:43 am
Hi,
I am getting the following error when i run DBCC CHECKDB on my database.
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.
DBCC results for 'DB_Name'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_Name'.
Msg 823, Level 24, State 3, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x00000000022000 in file 'E:\Ppath_UAT.mdf'. 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.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000004fa000 in file 'E:\Ppath_UAT.mdf'. 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.
I ran DBCC CHECKDB to check for integrity errors. As i got the following error when trying to run select on DB
Message
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000004fa000 in file 'E:\PBRS_UAT_DB\PBRS_UAT.mdf'. 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.
Thanks in Advance.
Sanjay
September 6, 2016 at 2:50 am
Is the E drive present and can you see databases and open files on that drive?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2016 at 3:45 am
Yes, E drive is present. I can't see database files, either by Properties or by sys.filegroupgs(I get the above mentioned error - second error)
September 6, 2016 at 4:19 am
Not asking if you can see the properties of the database in SQL. with the errors mentioned it's obvious you can't.
Can you see the database files in the file system? Is the directory E:\PBRS_UAT_DB\ accessible, and does it have the files that it should? Can you see the file properties?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2016 at 5:59 am
No, I can't see the File properties
September 6, 2016 at 6:43 am
Speak to your SAN admin, storage admin or whoever is responsible for the storage on that server. If the files aren't accessible, then that will have to be fixed before you can address any SQL problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2016 at 10:37 pm
This was removed by the editor as SPAM
October 13, 2016 at 3:19 am
JasonClark (10/12/2016)
Try the below steps:- Copy database files to another location
- Delete main files
- Restart SQL Service.
- In management studio the database will be shown with "Recovery Pending" in front of its name, delete it.
- Attach your database with copied files of the first step.
Useless, potentially will make a problem worse as a corrupt database may not attach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2016 at 12:33 am
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.
In SQL Server, DBCC commands use internal read-only database snapshots. These database snapshots are created on the same drive where the corresponding database data files are located. Database snapshots grow in proportion to the amount of changed data in the database against which the DBCC commands run. If transactional activity continues on this database, the database snapshots that are created by DBCC commands may experience disk space issues. Because the database snapshot files and the actual data files reside on the same disk drive, both sets of files compete for disk space. In this case, application transactions or user transactions are given preference. The internal database snapshot used by DBCC is marked as suspect. Therefore, the DBCC commands experience errors and cannot finish.
Disk Space is one reason why writes to the internal database snapshot may fail. Other reasons such as OS error codes 1450 and 665 can also contribute to similar issues and render the internal database snapshot to a suspect state.
October 17, 2016 at 12:04 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply