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.
question no 1. what is your settings Data and log file settings are they are in same disk ? 2.what DBCC command did you run? 3.are you running DBCC command same time you run backup job? 4.what is your auto growth settings? 5.what recovery model you using for Database ? 6.how much disk space do you have ?
once you more specific about the questions i asked .i could help you out
answered Mar 16, 2017 at 05:22 PM
this error occurs when the hidden database snapshot runs out of space. You may have a look on this: http://sqlism.blogspot.com/2014/10/a-database-snapshot-cannot-be-created.html
answered Mar 20, 2017 at 06:19 AM