CHECKDB From Every Angle: Automate your database integrity checks with SMO

Quickie this morning – I was surfing some MVP blogs this morning and came across a series of posts by Allen White around using VB Script and SMO to automate regular maintenance jobs. His latest post contains code to regularly run consistency checks and more, and report any failures.

Very nice – check it out!

7 thoughts on “CHECKDB From Every Angle: Automate your database integrity checks with SMO

  1. Hi. I’ve been rewriting our db maintenance scripts in Powershell, and have come across something that I have yet to have explained. In powershell, $db.CheckTables() actually runs (according to Profiler) the command DBCC CHECKDB WITH NO_INFOMSGS. I have a couple of SQL2000 databases on which the CheckTables() command fails, but the DBCC command works. I have tested it as follows CheckTables() – fail. DBCC – works. CheckTables() – still fails. Any ideas?

  2. Actually, the CheckTables method requires a parameter, which your example doesn’t list, and this can be one of four values. ‘None’ will run DBCC CHECKDB WITH NO_INFOMSGS, as you mentioned. ‘Fast’ will run DBCC CHECKDB(N’Northwind’, REPAIR_FAST) WITH NO_INFOMSGS. ‘Rebuild’ will run DBCC CHECKDB(N’Northwind’, REPAIR_REBUILD) WITH NO_INFOMSGS, and ‘AllowDataLoss’ will run DBCC CHECKDB(N’Northwind’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS. All but ‘None’ require the database to be in Single User mode, which may be the cause of your problem.

  3. Sorry, Allen, typo on my part. (It actaully wont run without a parameter). This is what I am running and the result I am getting.

    PS F:\mssql\Scripts\Powershell> $db.CheckTables(0)
    Exception calling "CheckTables" with "1" argument(s): "Check tables failed for Database ‘prd0Sylvan’. "
    At line:1 char:16
    + $db.CheckTables( <<<< 0)
    PS F:\mssql\Scripts\Powershell>

  4. Although now I’m just as confused. The error I am getting back is
    An exception occurred while executing a Transact-SQL statement or batch.
    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    The SQL it dies on (according to profiler) is
    DBCC DBREINDEX(N'[dbo].[holding]’, N”, 90)
    which works fine when I plug it into a query window.
    My Powershell code is
    foreach ($tbl in $tbls) {$tbl.RebuildIndexes(90)}
    Do I need to put the powershell equivalent of a GO statement (if such a thing exists) after each rebuildindexes command?

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.