April 2, 2014 at 1:25 pm
This is a VM SQL2008R2 SP2 system. We moved this db to this SQL Server from another SQL2008 last Friday and rebuilt indexes and checkdb has worked fine every day until this morning. This morning i got the below error and upon doing some searching on the Internet I found one that said restart SQL Server and it fixes this error for snapshots from CHECKDB. It took about 40 minutes for the db to recover but it came back online and I ran a full DBCCHECK DB and it came back with no errors. That was early this morning and now I run another CHECKDB and I get the same exact failure. The data drive and log drive have almost 200gig of free space so space isn't an issue. Any help on this issue would be much appreciated. I am doing some searching but am coming up with no resolutions other than to open a call with Microsoft support. This DB is 35 gig and we have 7 other databases that are smaller that are fine and have been for 2 months.
Here is the error from DBCC CHECKDB
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x0000015592e000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WLN99.mdf:MSSQL_DBCC5'. 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 1823, Level 16, State 1, Line 1
A database snapshot cannot be created because it failed to start.
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 3314, Level 17, State 3, Line 1
During undoing of a logged operation in database 'WLN99', an error occurred at log record ID (87009:12968:56). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
There is nothing in the SYSTEM EVENT LOG showing any problems.
The Application EVENT log shows this:
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x0000015592e000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WLN99.mdf:MSSQL_DBCC5'. 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.
During undoing of a logged operation in database 'WLN99', an error occurred at log record ID (87009:12968:56). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
April 2, 2014 at 1:47 pm
First thing that comes to mind is a disk space or (EDIT) VM max size config kind of problem.
April 2, 2014 at 1:54 pm
April 2, 2014 at 1:56 pm
Never thought of that... I have never created a db snapshot... In doing more research there maybe a Windows 2008 hotfix for this.
April 2, 2014 at 1:59 pm
That is what DBCC CHECKDB is doing, so try it for yourself and see if you get an error. If you succeed in creating one you can try to run DBCC CHECKDB on that snapshot.
April 4, 2014 at 6:54 am
Ended up opening a ticket with Microsoft Support on this case as the CHECKDB utility would not run on this one specific db twice.... There is a Windows patch for this. I applied it last night and so far the DBCC CHECKDB utility has been running fine.
April 4, 2014 at 8:33 am
April 4, 2014 at 8:40 am
I hope this fixes the issue. This SQL Server is only about 3 months old... has 7 under 10 gig dbs on it and this one is 100gig MDF file but only 20 gig of data in it. Tech said that if this doesn't fix the issue then we will ahve to defrag the volume.
October 21, 2014 at 2:03 pm
http://sqlism.blogspot.com/2014/10/a-database-snapshot-cannot-be-created.html
Sometimes an issue arises when the hidden database snapshot runs out of space. Because it’s implemented using alternate streams of the existing data files, the database snapshot consumes space from the same location as the existing data files. If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow. In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error. An example of this is shown here (the errors can vary depending on the exact point at which the database snapshot runs out of space):
February 4, 2016 at 11:19 pm
Logical and physical consistency is being checked by DBCC CHECKDB, like index relationships, rows, allocation pages, system table referential integrity, and other formation verification. During this process if any above-mentioned checks fail, then you may receive errors. These errors could fluctuate from corruption of file like corrupted pages in memory, driver issues, underlying hardware system issue, or issue with SQL Server Engine. While working with SQL you may receive following DBCC CHECKDB command as mentioned below;
DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.
If you have back-up you can restore from there but if you do not have any back-up then most recommended option would be to opt for some third party repair tool. And SQL Database File Repair Tool is best available for this purpose. It effectively scans and repairs DBCC CHECKDB error and also repairs damaged database and retrieve inaccessible objects in NDF and MDF database files. It restore and repair corrupt database files along with stored procedures, keys, views, triggers, indexes, tables, deleted records etc.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply