1

My database in Production has 28ndf files in different Drives.And on T drive test_audit20.ndf to test_audit23.ndf files only. and file test_audit20 and test_audit21.ndf autogrowth is none.
While executing checkdb command on test_audit db I am getting error :

Msg 1823, Level 16, State 6, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, 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 8921, Level 16, State 3, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 3313, Level 21, State 1, Line 1
During redoing of a logged operation in database 'test_audit', an error occurred at log record ID (19372:991854:10).
Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 9001, Level 21, State 7, Line 1
The log for database 'test_audit' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 5128, Level 17, State 2, Line 1
Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I am not sure why it would need to write to this disk.

It could be that auto-grow is turned on for one of the files on this disk. We could turn that off and it might begin working .

Currently I have X and Y drives that are the “active” data drives, where growth should happen. Those are also almost full (3TB each) and I have to stand up some new disk drives and consolidate the data files to them. I have hit our limit with AWS for provisioned terabytes in this region.

Additionally, I have been manually extending these data files every week. I cannot rely on SQL’s auto-grow as I have mentioned before, as it waits until all files are full. This causes data writes to be centralized into one disk/file and has a dramatic negative impact on performance when this happens – and causes a surge in tickets to Customer Support and impacts SLAs.

My question is can I try turning auto-grow off on any data files on the T drive, and try again? will it cause any issue or is there any other way.

Improve this question
3
  • 1
    Sounds like (perf issue on growth) as though you don't have IFI enabled, and quiet possibly not trace flag 1117 enabled which grows all files in a filegroup equally. – Nic Mar 10 '17 at 20:31
  • IFI seems interesting as I never heard but just read about that.By enabling will there be any negative impact on performance. – somu Mar 10 '17 at 20:48
  • None at all. Logs still get zeroed out, but the data files won't (unless you are running TDE) and that significantly reduces disk i/o and prevents the kinds of stalls that growths cause otherwise. – Nic Mar 10 '17 at 21:07
3

The message is telling you why it failed, here is the root cause of the failure:

Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.

Looks like the T volume runs out of space.

I am not sure why it would need to write to this disk.

A hidden snapshot is taken and crash recovery run on the databases to bring it to a consistent state so that checkdb can run against the hidden snapshot copy of the database. This is how it works when checkdb is run online. There is the tablock option which is called 'offline' as it takes locks where the online way doesn't.

You are mostly correct. It doesn't force all writes to a single data file, however it is recalculating the proportional fill values which leads to hotspotting in a few number of files. This is known (though it seems no one cares) and that's why we had trace flags to grow the entire filegroup all together (TF 1118). This was changed and is now a database option in 2016.

Manually growing the data files each week seems excessive. If you know it's just going to grow by another TB each week, why grow it so low? If Performance Volume Maintenance Tasks is given to the service account or service sid you should be able to grow the data files fairly large with little impact.

My question is can I try turning auto-grow off on any data files on the T drive, and try again?will it cause any issue or is there any other way.

Autogrow settings aren't your issue, sparse writes and running out of space on the volume, is.

Update:

so what will be best way to deal these type of issues.shall I add more space?

Maybe. Depends.

If the datafiles are already on monolithic volumes, then I'd work on migrating them off the same volume. You can use mount points, storage spaces, etc., but having available space is what you need. Again, this has nothing to do with your autogrow settings and everything to do with the amount of free space available and the amount of space required to make the snapshots consistent.

What else could you do?

Well, you don't have to run CheckDB() all at once. You can break it up into smaller bite sized chunks of checks, things like using checkalloc, checkcatalog, checktable, etc. See the link here, I don't need to remake the wheel as Paul already has great articles.

and in this case more ndf files are there then why it is writing in 23rd ndf files ,it should write to 28.ndf in X drive

The root cause of the issue is that you're unfamiliar with how CheckDB works, and that's okish. Since you know there is now a level of understanding needed, check the link I gave and read Paul's posts.

The snapshot files will be made for each data file, it's a snapshot and not actually writing to the database itself. When you read Paul's series I believe you will understand what and why - along with this answer.

Improve this answer
3
  • Test_audit23.ndf autogrowth is set to 10000 MB,Unlimited.so what will be best way to deal these type of issues.shall I add more space?and in this case more ndf files are there then why it is writing in 23rd ndf files ,it should write to 28.ndf in X drive – somu Mar 10 '17 at 19:37
  • Autogrow settings aren't your issue, sparse writes and running out of space on the volume, is. So why would applications cause SQL Server to attempt to write to these sparse files? Shouldn’t only the snapshot operation or checkdb command be doing this? Would executing a query in Snapshot Isolation mode also attempt to write/create these sparse files? – somu Mar 10 '17 at 21:33
  • Applications don't. Executing CheckDB does. @somu – Sean Gallardy - Retired User Mar 11 '17 at 0:21
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.