none
Database locked?

    Question

  • Hi,

    What happens exactly during DBCC CHECKDB? I mean an internal snapshot is created and DBCC CHECKDB is running on this internal snapshot, right? But imagine the following, DBCC CHECKDB is running and a hardware (let´s asume disk) failure occurs, which results in the fact that the internal snapshot remains on the disk. So DBCC CHECKDB could not finish. Is in this case the user database still available for read/write actions? I mean is the user database not locked? So users can still acces the database and read from and/or write to the user database?

    I´m asking this because I had such case recently. The customer complained that the database was hard to access. I still think this was due to the hardware failure that occured. The only thing to get rid of this internal snapshot was to reboot the server which threw away this internal snapshot file. After that the database could be accessed again.

    greetings Marco

    Thursday, February 08, 2018 6:59 PM

Answers

  • Hi Marco,

    >> What happens exactly during DBCC CHECKDB? I mean an internal snapshot is created and DBCC CHECKDB is running on this internal snapshot, right?

    Yes, DBCC CHECKDB uses an internal database snapshot to provide the required transactional consistency. Once the database snapshot is created, DBCC CHECKDB is guaranteed a transactionally consistent view of the database and can run the various check algorithms against the database snapshot. About what happens exactly during DBCC CHECKDB, please refer to CHECKDB From Every Angle: Complete description of all CHECKDB stages written by Paul Randal.

    >> Is in this case the user database still available for read/write actions? I mean is the user database not locked? So users can still acces the database and read from and/or write to the user database?

    I agree with you. Based on my understanding, if hardware issue occurs, it will cause inconsistency without making the database unreadable by SQL Server.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by marqo Sunday, February 11, 2018 3:28 PM
    • Unmarked as answer by marqo Sunday, February 11, 2018 3:28 PM
    • Marked as answer by marqo Sunday, February 11, 2018 3:28 PM
    Friday, February 09, 2018 3:10 AM

All replies

  • Hi Marco,

    >> What happens exactly during DBCC CHECKDB? I mean an internal snapshot is created and DBCC CHECKDB is running on this internal snapshot, right?

    Yes, DBCC CHECKDB uses an internal database snapshot to provide the required transactional consistency. Once the database snapshot is created, DBCC CHECKDB is guaranteed a transactionally consistent view of the database and can run the various check algorithms against the database snapshot. About what happens exactly during DBCC CHECKDB, please refer to CHECKDB From Every Angle: Complete description of all CHECKDB stages written by Paul Randal.

    >> Is in this case the user database still available for read/write actions? I mean is the user database not locked? So users can still acces the database and read from and/or write to the user database?

    I agree with you. Based on my understanding, if hardware issue occurs, it will cause inconsistency without making the database unreadable by SQL Server.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by marqo Sunday, February 11, 2018 3:28 PM
    • Unmarked as answer by marqo Sunday, February 11, 2018 3:28 PM
    • Marked as answer by marqo Sunday, February 11, 2018 3:28 PM
    Friday, February 09, 2018 3:10 AM
  • If hardware failure occurs on disk where system files or data files or system files are there you may see whole server going down and you would not be able to access at all. The hardware failure leads to blackout kind of situation, ofcourse it depends degree of problem.

    There must be some other issue while accessing database and it is highly unlikely because of hardware failure. Yes checkdb causes massive load and this can lead to server being super slow.

    To dig more into this I would like to see erorrlog

    sp_readerrorlog


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, February 09, 2018 7:50 AM
    Moderator