Post reply

Run DBCC CHECKDB using Snapshot of the database.

  • December 8, 2014 at 11:58 am

    #293153

    I have huge database on prod. One time I tried to run DBCC CHECKDB, it took more than a day. My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB? will doing this interfere production database? Is this a good idea or is there any better way you guys like to share?

    Last thing, I don’t have option to make copy of the database on a test server and run it there.

  • December 8, 2014 at 2:38 pm

    #1763384

    can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB?

    Yes. It's easy, just like it sounds: create the snapshot, run dbcc checkdb on it.

    will doing this interfere production database?

    Yes. Every page write becomes 2 writes, as SQL does a copy-on-write option in the background to the snapshot db.[/quote]

    Is this a good idea or is there any better way you guys like to share?

    You could try limiting the checkdb to no indexes and physical check only. This should still catch almost all common critical data errors.

    DBCC CHECKDB ( <database_name>, NOINDEX ) WITH NO_INFOMSGS, PHYSICAL_ONLY

    I believe you can also use the TABLOCK option for even more efficiency because I think it locks only the snapshot db, not the main db, but I'm not 100% sure of that.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 8, 2014 at 5:41 pm

    #1763406

    ScottPletcher (12/8/2014)

    can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB?

    Yes. It's easy, just like it sounds: create the snapshot, run dbcc checkdb on it.

    You don't have to manually create a snapshot.

    You can start from reading the documentation:

    http://msdn.microsoft.com/en-us/library/ms176064%28v=sql.120%29.aspx

    Internal Database Snapshot

    DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks.

    Alex Suprun

  • December 9, 2014 at 1:18 am

    #1763433

    Tac11 (12/8/2014)

    My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB?

    You can, but there's little point. CheckDB, in it's normal operating mode, creates a snapshot of the database and runs against that snapshot. You're not going to gain anything by manually creating a snapshot other than the ability to specify where the snapshot files go.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2014 at 6:32 am

    #1763496

    Thank you ScottPletcher, Alexander Suprun and GilaMonster

    @ GilaMonster, Should I put Snapshot database's Data and Log files point (put it) to another server? Is it possible? I just don't want to give the Prod server extra load.

  • December 10, 2014 at 2:16 am

    #1763707

    Tac11 (12/9/2014)

    Should I put Snapshot database's Data and Log files point (put it) to another server? Is it possible?

    No.

    I just don't want to give the Prod server extra load.

    Then restore a backup elsewhere and checkDB that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 10, 2014 at 5:07 am

    #1763741

    http://sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb

    Argenis Fernandez did an excellent webinar for this back in March, you could ask him for the slides.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • May 8, 2015 at 10:40 am

    #1796658

    Hi Gail,

    We run DBCC CHECKDB as a job in the evening when there is less activities. All the jobs are run with a domain account that have local administrator rights. Recently, CHECKDB suddenly start to fail for certain databases (whether the db is small or large), however, errorlog does not show database is in suspected mode or any other errors. I tried to google the error (see below) we are having but just can't find any matches. All the errors have same pattern as below. Have you seen this? Is there a fix for this?

    I've also read a bunch of documents that CHECKDB creates snapshots in order to do it's works. At one point, I thought there is not enough disk space. But it can't be because we have plenty of disk space.

    FYI, we use Windows Server 2012 DataCenter (64-bit) and SQL Server 2012 Enterprise Edition (64-bit). All these run under VMWare.

    Here is the error I'm talking about:

    FCB::Open failed: Could not open file g:\SQLData\mhpprodsrv01\EXP_PROD_WEBFORMS.NDF:MSSQL_DBCC10 for file number 3. OS error: 32(The process cannot access the file because it is being used by another process.).[/b]

    Thanks in advance,

    Jimmy

  • May 8, 2015 at 10:48 am

    #1796661

    Please post new questions in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 8, 2015 at 10:58 am

    #1796664

    Thanks. I reposted under "SQL Server 2012 - General" with the topic as "DBCC CHECKDB - snapshot error".

Viewing 10 posts - 1 through 9 (of 9 total)

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