Home
  •  

AK80671

Monitor_AK80671 (UnitMonitor)

Rule for SQL Server database present without a clean consistency check

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=199778

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.Database
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server database present without a clean consistency check
<Details>
<Content>A SQL Server database does not have a record of an error free DBCC CHECKDB. This could be because DBCC CHECKDB has not been run since the database was created, or this could be because DBCC CHECKDB always found errors and those errors were not completely fixed. It is very important to make sure the database is clean. You can rely on reliability features like database CHECKSUM to detect problems immediately. But it is also important to perform a consistency check for the database using DBCC CHECKDB commands. Review the database that does not have a clean DBCC CHECKDB report and schedule a DBCC CHECKDB to run.</Content>
<CollectedInformation>
<Info>
<Name>Database name</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Database creation time</Name>
<Value>{1}</Value>
</Info>
<Info>
<Name>Time elapsed without a clean checkdb (Days)</Name>
<Value>{2}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.4;

Source Code:

<UnitMonitor ID="Monitor_AK80671" Comment="SupportTopic=TBD;VersionNumber=1.0.0.4;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessagef067258cd69548e3bd54ecd9fb864fd5">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='DatabaseCreateDate']$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='DatabaseCreateElapsed']$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AK80671.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>InDatabaseName</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</Value>

      </Parameter>

      <Parameter>

        <Name>InConnectionString</Name>

        <Value>$Target/Host/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($InDatabaseName,$InConnectionString)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "InDatabaseName" $InDatabaseName

$scriptargs | add-member NoteProperty "InConnectionString" $InConnectionString



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "LastErrorFreeCheckDays" 0

$scriptoutput | add-member NoteProperty "DatabaseCreateDate" ""

$scriptoutput | add-member NoteProperty "DatabaseCreateElapsed" ""

#---------------------------------------------------------------------------

# Helper: prepare SqlCommand object for other functions

#---------------------------------------------------------------------------

function PrepareCommand() {

	param($queryParams)



	$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

	$oSQLCommand.Connection = $queryParams.Connection

	$oSQLCommand.CommandText = $queryParams.Query

	[void]$oSQLCommand.Parameters.Add("@dbname", $queryParams.DatabaseName)

	return $oSQLCommand

}





#---------------------------------------------------------------------------

# 

#-- database is online

#-- database is not a mirror db

#-- database cant be a snapshot db

#-- database can't tempdb

#

#---------------------------------------------------------------------------

function IsDatabaseStatusCorrect() {

	param($queryParams)

	

	$queryParams.Query = "SELECT 1 " + 

						"FROM sys.databases a " +  

						"JOIN sys.database_mirroring b ON b.database_id = a.database_id " + 

						"WHERE a.state = 0 " + 

							"AND b.mirroring_guid IS NULL " + 

							"AND source_database_id is null " + 

							"AND a.name not in ('tempdb') " + 

							"AND a.name = @dbname "	

								

	return ((PrepareCommand $queryParams).ExecuteScalar() -ne $null)

}



#---------------------------------------------------------------------------

#

# Check when the CheckDB command was run last

# return: number of days from last run

#	!in case of error return -1 days so the alert will not be raised

#

#---------------------------------------------------------------------------

function GetLastErrorFreeCheckDays() {

	param($queryParams)

		

	# Trap $reader.read() exception to handle Unicode bug

	trap [System.Management.Automation.MethodInvocationException] {

		continue;

	}

		

	$queryParams.Query = "DBCC DBINFO ( @dbName ) WITH TABLERESULTS"

	$reader = (PrepareCommand $queryParams).ExecuteReader()

	

	#

	# Bug about Unicode chars in database name exeuting DBCC DBINFO:

	#   http://connect.microsoft.com/SQLServer/feedback/details/349846/dbcc-dbinfo-bug-msg-407-level-16-state-1-line-1

	# Here the workaround: assumed that Unicode databases are correct to avoid noise

	#

	[int]$days = -1 

	while ($reader.read()) 

	{

		if (($reader["ParentObject"] -eq "DBINFO STRUCTURE:") -and ($reader["Field"] -eq "dbi_dbccLastKnownGood")) 

		{

			$days = ([System.DateTime]::Parse($reader["Value"]) - [System.DateTime]::Parse("1900-01-01")).Days

		}

	}

	$reader.Close()

	

	$days

}



#---------------------------------------------------------------------------

# Helper:  Returns Database creation date and elapsed time from creation

#---------------------------------------------------------------------------

function GetDatabaseCreationTime() {

	param($queryParams)

	

	$queryParams.Query = "SELECT CAST(Create_Date as varchar(50)) as DatabaseCreateDate, DATEDIFF(DAY, Create_Date , GETDATE()) as DatabaseCreateElapsed from sys.databases WHERE name = @dbName"

	$reader = (PrepareCommand $queryParams).ExecuteReader()

	

	$res = @{

		"DatabaseCreateDate" = [string]::Empty;

		"DatabaseCreateElapsed" = [string]::Empty

	}

	

	if ($reader.HasRows -and $reader.read()) {

		$res["DatabaseCreateDate"] = $reader["DatabaseCreateDate"]

		$res["DatabaseCreateElapsed"] = $reader["DatabaseCreateElapsed"]

	}

	

	$reader.Close()

	

	return $res

}



#---------------------------------------------------------------------------

# MAIN Rule function

#---------------------------------------------------------------------------

function AdvisorRule($scriptargs, $scriptoutput)

{

	$queryParams = New-Object psobject

	$queryParams | Add-Member NoteProperty "Connection" $null

	$queryParams | Add-Member NoteProperty "DatabaseName" [string]::Empty

	$queryParams | Add-Member NoteProperty "Query" [string]::Empty



	$queryParams.DatabaseName = $scriptargs.InDatabaseName

	

	# Open connection to database

	$oConnection = New-Object System.Data.SqlClient.SqlConnection

	$oConnection.ConnectionString = "Server=" + $scriptargs.InConnectionString + ";Database=master;Integrated Security=true"

	$oConnection.Open()



	$queryParams.Connection = $oConnection



	# STEP 0: set days = -1 to prevent UNRESOLVED error in ExpressionFilter and Alert

	#	- if don't fill PropertyBag the ExpressionFilter will fail wiht UNRESOLVED property - not good

	#	- if set 0 then Alert will be raised

	[int]$lastErrorFreeCheckDays = -1



	# STEP 1: Check the database status

	if (IsDatabaseStatusCorrect $queryParams ) 

	{

		#STEP 2: Get number of days from last CHECKDB call

		$lastErrorFreeCheckDays = GetLastErrorFreeCheckDays $queryParams



		#STEP 3: Get the database creation time

		#	- As these properties are not used in expression filter - we can don't fill them by default

		$dbTimeInfo = GetDatabaseCreationTime $queryParams



		$scriptoutput.DatabaseCreateDate = $dbTimeInfo["DatabaseCreateDate"]

		$scriptoutput.DatabaseCreateElapsed = $dbTimeInfo["DatabaseCreateElapsed"]

	}



	$scriptoutput.LastErrorFreeCheckDays = $lastErrorFreeCheckDays

	$oConnection.Close()

}



AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.LastErrorFreeCheckDays -ne $null)

{

	$bag.AddValue("LastErrorFreeCheckDays", $scriptoutput.LastErrorFreeCheckDays)

}



if ($scriptoutput.DatabaseCreateDate -ne $null)

{

	$bag.AddValue("DatabaseCreateDate", $scriptoutput.DatabaseCreateDate)

}



if ($scriptoutput.DatabaseCreateElapsed -ne $null)

{

	$bag.AddValue("DatabaseCreateElapsed", $scriptoutput.DatabaseCreateElapsed)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86396</Schedule>

    <ErrorExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Integer">Property[@Name='LastErrorFreeCheckDays']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Integer">0</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <Value Type="Boolean">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/StandBy$</Value>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">false</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </And>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <And>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="Integer">Property[@Name='LastErrorFreeCheckDays']</XPathQuery>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="Integer">0</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <Value Type="Boolean">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/StandBy$</Value>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="Boolean">false</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

          </And>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>