Day 29 of 31 Days of SQL Server Backup and Restore using PowerShell: Why I don’t use the checktables SMO method

2 different occasions at SQL Saturday Cambridge 2013 made me realise that I needed to ‘justify’ my use of Invoke-SQLCmd to run DBCC back on Day 21 of 31 Days of SQL Server Backup and Restore using PowerShell: Verfiying a restored database. One was during my session (Slides and scripts here), and the other was during Seb Matthews session where he said to view every use of Invoke-SQLCmd as a failure.

The first thing I don’t like about this particular message is what happens when you check a ‘good’ database:

Import-Module sqlps -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")

$db = $sqlsvr.Databases.item("GoodDB")

$db.CheckTables("None")

If you run this, you’ll notice you get nothing back, not a thing. This is because CheckTables runs with NO_INFOMSGS as default. And there’s no way to override it. This might work for some applications, but in some environments you would want to have an informational messages returned and recorded, as proof that all checks were ran and passed.

That’s strike 1 for me.

If you’re wondering what the parameter to CheckTables is, it’s the repair level. Accepted values are:

  • None
  • Fast
  • Rebuild
  • AllowDataLoss

Which operate in the same way as they do under T-SQL

If you’re lucky enough not to have a corrupt database to play with, then Paul Randal has some you can download: (Paul Randal, SQLSkills.com, Corrupt SQL Server Databases). Assuming you’ve restored the db as chkDBCC then you can check it as so:

Import-Module sqlps -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")

$db = $sqlsvr.Databases.item("chkDBCC")

$db.CheckTables("None")

This time we will get some output, unfortunately this time a not hugely useful generic error message:

Exception calling "CheckTables" with "1" argument(s): "Check tables failed for Database 'chkdbcc'. "
At line:5 char:1
+ $db.CheckTables("None")
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : Fai

Strike 2

We can improve on this is we actually examine the error in a bit more detail by putting it in a Try, Catch block:

Import-Module sqlps -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")
$db = $sqlsvr.Databases.item("chkdbcc")

try{
    $db.CheckTables("None")
}
catch{[System.Exception]
  $err = $_.Exception
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    write-output $err.Message
    };
}

And now we get a more informative response:

Check tables failed for Database 'chkdbcc'.
An exception occurred while executing a Transact-SQL statement or batch.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in
 sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in
 sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'chkDBCC'.

To get the full information returned in our previous examples:

Error       : 8992
Level       : 16
State       : 1
MessageText : Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row
              (object_id=1977058079,column_id=2) in sys.columns does not have a matching row
              (object_id=1977058079) in sys.objects.
RepairLevel :
Status      : 16
DbId        : 23
DbFragId    : 1
ObjectId    : 0
IndexId     : -1
PartitionId : 0
AllocUnitId : 0
RidDbId     : 23
RidPruId    : 0
File        : 0
Page        : 0
Slot        : 0
RefDbId     : 23
RefPruId    : 0
RefFile     : 0
RefPage     : 0
RefSlot     : 0
Allocation  : 1

Well, we can’t. As well as enforcing the NO_INFOMSGS clause, CheckTables doesn’t allow the use of TABLERESULTS.

Which is strike 3, and it’s out of here!

This is all personal opinion, but this is one of the very few DBA tasks where I will always opt to use Invoke-SQLCmd over an SMO object.

Hopefully we might see a better SMO option with the release of SQL Server 2014…..

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *