APIPythonOpen-Source Solutions

Hi all

Has anyone tried running DBCC CHECKDB using Delphix...is it viable?  We were thinking of running physical only on our prod dbs and provisioning to delphix to perform a full DBCC check.

Is there a way to automate the provisioning of DBs?  So the DB is provisioned, with a post script to run DBCC CHECK, and then delete the vdb automatically with no manual intevention?
#sql_server
#sql
#api
#delphix_api
#dbbc
#delphix

Though I have never done it, I don't see any reason wny you couldn't execute dbcc on a VDB leveraging a post script. You could write a script that executes against the Delphix API to accomplish what you are asking in regards to the automatic teardown. I figure this would be the way to go, as you likely need conditional logic to handle a vdb where the DBCC check fails.

Mark,

Running DBCCs against the VDBs is a great way of moving the IO demand off of the production systems. As Adam stated, if you want to create the VDB and then destroyed afterwards, you'll need to create some scripts that call the Delphix APIs.

If you don't mind the VDB being left on the system, you can run the following and modify it based on your processes or policies:

1. Create a VDB on the environment you want to run the DBCCs on
2. When configuring the VDB, enter a Post script that executes the DBCC command
3. Create a VDB Refresh policy to refresh the VDB

For my test, I entered the following for the Post script:
C:\MSSQL\PrePost\DBCC.ps1

The "DBCC.ps1" script contains the following:
function die {    "Error: $($args[0])"
    exit 1
}

function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}

sqlcmd -b -U sa -P password -S "tcp:WIN2012R2STD\SQL2012,49915" -q "use Delphix_Admin_Test5_vdb10; DBCC CHECKDB"

VerifySuccess "sqlcmd failed to insert values"

In my script I've hard-coded the VDB name (as of version 4.0 you can use environment variables for the VDB instead). After the VDB is created the Post script will be called and a DBCC will run against the database. And when the VDB refresh policy executes, it will also call the same script once the VDB is refreshed. Here's the sql log information confirming the DBCC executed:

Date 7/11/2014 8:30:11 PMLog SQL Server (Current - 7/8/2014 11:20:00 PM)

Source spid57

Message
DBCC CHECKDB (Delphix_Admin_Test5_vdb10) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.  Internal database snapshot has split point LSN = 0000001e:0000164e:0001 and first LSN = 0000001e:0000164c:0001.

And you can switch from calling the DBCC directly in the ps1 script and instead call a sproc, passing the VDBs name and capturing the results in a temp or table variable to process the output for any errors.

Thanks...that is very very useful.  We are soon upgrading to version 4..looking forward to that!

Hi Darin...I'm getting an error with the DBCC CHECK

Msg 3737, Level 16, State 0, Line 1Could not delete file 'C:\Program Files\Delphix\DelphixConnector\564d490d-324c-eb3d-ffb5-8a67f2c9dd39-vdb-630\DATA\db\FrontOffice_Data_Primary1.mdf:MSSQL_DBCC11'. See the SQL Server error log for more information.

Did you get this at all?

Hi Mark,

I did and do when I perform a DBCC CHECKDB against a VDB. When VDBs are provisioned, the SQL files for the database have a deny delete placed on them. This prevents the files from being deleted if a DBA mistakenly deleted the VDB using SSMS and not from the application. So the DBCC hits the same issue after it's finished checking the database. But I've actually never seen the file it's complaining about left on the LUN, so it seems it's more of an informational than a fatal error.

Here's the output from a CHECKDB I ran:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'AGDatabase4_vdb1'.
Msg 3737, Level 16, State 0, Line 1
Could not delete file 'C:\Program Files\Delphix\DelphixConnector\564d4a9b-a214-d340-f72e-6e897f240548-vdb-1\DATA\db\AGDatabase4.mdf:MSSQL_DBCC9'. See the SQL Server error log for more information.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can see the actual DBCC process was successful and no issues were found. And here's a DIR of the file system after running showing there was actually no file left behind:

Directory of C:\Program Files\Delphix\DelphixConnector\564d4a9b-a214-d340-f72e-6e897f240548-vdb-1\DATA\db

07/23/2014  03:44 PM    <DIR>          .
07/23/2014  03:44 PM    <DIR>          ..
07/23/2014  03:47 PM        31,457,280 AGDatabase4.mdf
07/23/2014  03:44 PM        31,457,280 AGDatabase4_log.ldf
               2 File(s)     62,914,560 bytes
               2 Dir(s)  33,553,668,448,256 bytes free

So, if you capturing the DBCC output and then parsing for errors, you can skip over any error that mentions a file was not able to be deleted.

Thanks Darin :)