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.
Best Answer
The message is telling you why it failed, here is the root cause of the failure:
Looks like the T volume runs out of space.
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.
Autogrow settings aren't your issue, sparse writes and running out of space on the volume, is.
Update:
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.
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.