A standard DBA task is to check the databases, also their integrity with the console command DBCC CHECKDB.
The output like below is (somewhat) nicely formatted for human reading, but I would like to catch it inside the automation so that I can process and log the output.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1970 rows in 24 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 346 rows in 4 pages for object "sys.sysrowsets".
...
DBCC results for 'Person.vStateProvinceCountryRegion'.
There are 181 rows in 2 pages for object "Person.vStateProvinceCountryRegion".
DBCC results for 'sys.plan_persist_query'.
There are 0 rows in 0 pages for object "sys.plan_persist_query".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.(example on DBCC CHECKDB output from the database [AdventureWorks2014])
The PowerShell code is
$DatabaseName = 'AdventureWorks2014'
$Sql = "DBCC CHECKDB ([$DatabaseName]);"
$CheckDbMessages = @()
$CheckDbWatch = [System.Diagnostics.Stopwatch]::StartNew()
SQLCMD.EXE -E -Q $Sql |
ForEach-Object {
$CheckDbMessages += $_
" {0:s}Z $_" -f ([System.DateTime]::UtcNow)
}
$CheckDbWatch.Stop()
"{0:s}Z Database [$DatabaseName] integrity check done in $($CheckDbWatch.Elapsed.ToString()) [hh:mm:ss.ddd]." -f ([System.DateTime]::UtcNow)
if ($CheckDbMessages[($CheckDbMessages.Count) - 2] -clike 'CHECKDB found 0 allocation errors and 0 consistency errors in database *') {
"{0:s}Z Database [$DatabaseName] integrity check is OK." -f ([System.DateTime]::UtcNow)
}
else {
"{0:s}Z Error in integrity check of the database [$DatabaseName]:`n $($CheckDbMessages[($CheckDbMessages.Count) - 2])" -f ([System.DateTime]::UtcNow)
throw "Integrity check of the database [$DatabaseName] failed."
}(example on PowerShell script to execute DBCC CHECKDB)
I have put it inside a (advanced) function, but that is not shown here as is is the DBCC CHECKDB command execution that is the subject. I can only recommend that you take a serious look on PowerShell advanced functions. The best introduction that I still go back to is the book „Learn PowerShell Toolmaking in a Month of Lunches“ by Don Jones and Jeffery Hicks.
The variable
$DatabaseName is a String object containing the name of the database to check.The script above does not check the prerequisites to the DBCC CHECKDB command. 2014-10-09T17:55:04Z DBCC results for 'AdventureWorks2014'.
2014-10-09T17:55:04Z Service Broker Msg 9675, State 1: Message Types analyzed: 14.
2014-10-09T17:55:04Z Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
2014-10-09T17:55:04Z Service Broker Msg 9667, State 1: Services analyzed: 3.
2014-10-09T17:55:04Z Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
2014-10-09T17:55:04Z Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
2014-10-09T17:55:04Z Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
2014-10-09T17:55:04Z Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
2014-10-09T17:55:04Z Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
2014-10-09T17:55:04Z DBCC results for 'sys.sysrscols'.
2014-10-09T17:55:04Z There are 1970 rows in 24 pages for object "sys.sysrscols".
2014-10-09T17:55:04Z DBCC results for 'sys.sysrowsets'.
2014-10-09T17:55:04Z There are 346 rows in 4 pages for object "sys.sysrowsets".
...
2014-10-09T17:55:04Z DBCC results for 'Person.vStateProvinceCountryRegion'.
2014-10-09T17:55:04Z There are 181 rows in 2 pages for object "Person.vStateProvinceCountryRegion".
2014-10-09T17:55:04Z DBCC results for 'sys.plan_persist_query'.
2014-10-09T17:55:04Z There are 0 rows in 0 pages for object "sys.plan_persist_query".
2014-10-09T17:55:04Z CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'.
2014-10-09T17:55:04Z DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2014-10-09T17:55:04Z Database [AdventureWorks2014] integrity check done in 00:00:04.1890229 [hh:mm:ss.ddd].
2014-10-09T17:55:04Z Database [AdventureWorks2014] integrity check is OK.(example on DBCC CHECKDB output from script - AdventureWorks)
Using the good-old SQLCMD.EXE gives the output to the default output stream, where the output then can be processed in the automation by PowerShell.
The SQL Server PowerShell module SQLPS CmdLet Invoke-SqlCmd can also execute the command DBCC CHECKDB, and by setting the -Verbose flag the output is shown - but it is in the Verbose stream and can not be accessed direct in the automation for further processing.
Streaming the Verbose to a file with a redirection operator is possible, but introduces extra complexity...
With ADO.NET the challenge is the same as with the CmdLet Invoke-SqlCmd, and I guess that it is because the CmdLet is constructed in .NET using ADO.NET.
Actually it is a general challenge to catch T-SQL output shown as messages in SQL Server Management Studio. This is because the output is sent async as events.
It is possible to build a strict .NET solution by using SqlInfoMessageEventHandler but I find the solution with SQLCMD.EXE more simple, and that I do like.
The option TABLERESULTS to the command DBCC CHECKDB is not documented by Microsoft, but widely recognized. I do not use the TABLERESULTS option in this solution as the results are delivered during execution but af one answer when the execution is finish. This is the behavior in all cases, also with ADO.NET ExecuteReader.
I have some larger databases, where a check takes several hours and I would like to have a log of the execution. Is everything running? Any errors corrected up til now? and so on...
This is also why I add a timestamp to each message line from DBCC CHECKDB.
Posts
