CHECKDB failed on large database - could not create snapshot

  • i'm looking for some information about this error. i've read many articles on this and i'm still searching for a workaround.

    checkdb has began to fail for one of our large dbs - 550GB in size (440GB db, 110GB log):

    Date and time: 2016-06-19 20:00:01

    Command: DBCC CHECKDB ([XXX]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY

    Msg 5269, Level 16, State 1, Server XXXXXXX\XXX, Line 1

    Check terminated. The transient database snapshot for database 'XXX' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.

    from what i read online, this is due to lack of space to create a snapshot. the data drive is 500GB, 431GB used, 69DB free. so how much space is required for a snapshot? how to calculate? i can't find this information.

    i tried using the TABLOCK option but that also fails:

    Date and time: 2016-07-02 14:30:00

    Command: DBCC CHECKDB ([XXX]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, TABLOCK

    Msg 5030, Level 16, State 12, Server XXXXXXX\XXX Line 1

    The database could not be exclusively locked to perform the operation.

    Msg 7926, Level 16, State 1, Server XXXXXXX\XXX, 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.

    Outcome: Failed

    Duration: 00:00:20

    Date and time: 2016-07-02 14:30:20

    it seems at this point either i need to allocate gobs of extra space for the snapshot or we need to shutdown the application just to run the checkdb. neither are very good options at this point. i tried to manually create a snapshot on a shared network disk but it fails too. seems i'm blocked at every turn.

    any ideas? thanks for any info!

  • The snapshot is a sparse file - it starts off small, and as data pages in the source database are changed, the old version of the pages are written to the sparse file and it grows. This is one reason why you should schedule your DBCC check for a quiet time. If, for example, you're attempting to do index maintenance at the same time, you could find your disk space being very quickly used. I've seen it happen and it's not pretty!

    John

  • JarJar (7/4/2016)


    i'm looking for some information about this error. i've read many articles on this and i'm still searching for a workaround.

    checkdb has began to fail for one of our large dbs - 550GB in size (440GB db, 110GB log):

    Date and time: 2016-06-19 20:00:01

    Command: DBCC CHECKDB ([XXX]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY

    Msg 5269, Level 16, State 1, Server XXXXXXX\XXX, Line 1

    Check terminated. The transient database snapshot for database 'XXX' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.

    from what i read online, this is due to lack of space to create a snapshot. the data drive is 500GB, 431GB used, 69DB free. so how much space is required for a snapshot? how to calculate? i can't find this information.

    i tried using the TABLOCK option but that also fails:

    Date and time: 2016-07-02 14:30:00

    Command: DBCC CHECKDB ([XXX]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, TABLOCK

    Msg 5030, Level 16, State 12, Server XXXXXXX\XXX Line 1

    The database could not be exclusively locked to perform the operation.

    Msg 7926, Level 16, State 1, Server XXXXXXX\XXX, 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.

    Outcome: Failed

    Duration: 00:00:20

    Date and time: 2016-07-02 14:30:20

    it seems at this point either i need to allocate gobs of extra space for the snapshot or we need to shutdown the application just to run the checkdb. neither are very good options at this point. i tried to manually create a snapshot on a shared network disk but it fails too. seems i'm blocked at every turn.

    any ideas? thanks for any info!

    I have DBCC Checkdb running on a 8 TB (compressed state), since the db is so large i have it split into multiple jobs and expand it over 5 days. Basically it does at table level. Actually as i am typing this message i working on an issue with the same error message that you got, in my experience you get that message either if you are running out of space or there is a lot of contention on that volume. In my case 9/10 cases where it failed with that message was due to contention. BTW i use Ola's DBCC checkdb script, i have it customized to add some more logging and few more knobs.

    I have been thinking this for a while, i might actually restore a full backup of the db on my DR server and have it run over there. It might or might not work in your case since i have the compression enabled at LUN level so the extra DB is not really taking any extra space. Hope this helps :).

  • thanks guys. i'm also using ola's scripts. i'm doing physical_only check dbs during the week and a full checkdb on the weekend. the period is supposed to be quiet but seems to be failing regularly at that time now. i'm adding disk space for the snapshot and moving the job to what should be an even quieter time. we'll see how it goes this weekend.

  • If you are on ENT Edition how about creating your own database snapshot so you can control where to place it and then run DBCC CHECKDB on that?

    You could be running out of space on the drive where the db files are as the snapshot is built on the same location.

  • BLOB_EATER (7/5/2016)


    If you are on ENT Edition how about creating your own database snapshot so you can control where to place it and then run DBCC CHECKDB on that?

    You could be running out of space on the drive where the db files are as the snapshot is built on the same location.

    yes we are. i read about that but it seems "six of one, half dozen of the other" to me. either way, i have to have a huge glob of disk space to store the snapshot. might was well share that disk with the data drive than have it sit empty 95% of the week.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply