we have SQL 2008 R2 standard edition. one of our databases has issue to run DBCC CHECKDB. the other databases don't have this DBCC CHECKDB error. the database doesn't have this error for 2 weeks after database restore from backup. it fails again after two weeks. we ran the DBCC CHECKDB against the same database after restore into different name without this errors.
any advise will be appreciated.
"CREATE FILE encountered operating system error 1393(The disk structure is corrupted and unreadable.) while attempting to open or create the physical file *.mdf:MSSQL_DBCC13"
we did. the DBCC CHECKDB run fine for a couple of weeks after database backup/restore. we use Melio file format. more details
"[SQLSTATE 42000] (Error 1823) CREATE FILE encountered operating system error 1393 (The disk structure is corrupted and unreadable.) while attempting to open or create the physical file '*.mdf:MSSQL_DBCC13'. [SQLSTATE 42000] (Error 5123) 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. [SQLSTATE 42000] (Error 7928) DBCC results for 'ArchiveBatchDetail'. [SQLSTATE 01000] (Error 2536) There are 4864 rows in 94 pages for object "ArchiveBatchDetail". [SQLSTATE 01000] (Error 2593) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed."
The answer is in the error message...the volume where you have the database does not support sparse files.
You have to configure the file system to allow for NTFS sparse files. I would review the configuration and verify that the volume you are using does in fact have sparse files enabled. If not, it needs to be enabled - if it is enabled and you are still having problems you need to follow up with the vendor.