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!
Happy Fourth of July to all who celebrate! I’m looking forward to a great view Thursday evening looking west along the Skagit Valley with some
(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more
(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more
(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more
We’ve added the ability to purchase a self-service health check using a credit card – no more waiting for a scoping call and then budget
Hello, my name is Paul, and I’m a high-functioning alcoholic. For the last ten years or so, I’ve been an alcoholic. A maintenance drinker –
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.
7 thoughts on “CHECKDB From Every Angle: Automate your database integrity checks with SMO”
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?
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.
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>
Sorry about the delay, but I was waiting for info from other sources, and when that didn’t come I had to dive into SMO to get the info I wanted. I wrote a blog post today – http://sqlblog.com/blogs/allen_white/archive/2009/06/08/handling-errors-in-powershell.aspx – that should help you find out what the real error is.
Allen
Brilliant. Thanks Allen, that’s precisely what I needed.
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?
OK. I’ve worked it out.
$srv.ConnectionContext.StatementTimeout=999999
and no more timeouts.