This monitor type detects potential problems with the operational database by checking the state of grooming and partitioning processes that could cause performance issues.
Knowledge Base article:
Summary
Monitor type for checking the grooming and partitioning process.
This workflow runs a script on a recurring schedule. The interval is the number of seconds that the workflow should wait from one run to the next. Change this value if you want the workflow to run more or less frequently.
Synctime
string
$Config/Synctime$
Sync time
TimeoutSeconds
int
$Config/TimeoutSeconds$
Timeout (seconds)
This workflow runs on script on a recurring schedule. The timeout is the number of seconds that the script is allowed to run before it will be forced to stop. Note: the timeout should always be less than the interval to ensure that multiple instances of the script do not run at the same time.
Threshold
double
$Config/Threshold$
Maximum days since last success
This workflow uses the "maximum days since last success" value as a threshold to determine state. Set this to the maximum number of days that can pass without partitioning and grooming completing successfully, before it is considered a problem.
'*************************************************************************
' $ScriptName: "PartitioningAndGroomingMonitor" $
'
' Purpose: This script checks for grooming and partiting processes.
'
' $File: PartitioningAndGroomingMonitor.vbs $
'*************************************************************************
Option Explicit
'Declarations
Dim objCN,objRS,strQuery, strCountOfJobsSucceeded
Dim oArgs,oAPI,oBag
Dim strDBServer,strDatabase
Dim strDaysLimitThreshold
'Set DB connection
Set objCN = CreateObject("ADODB.Connection")
objCN.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & _
strDatabase & ";Data Source=" & strDBServer & ""
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
Set oBag = oAPI.CreatePropertyBag()
Call oBag.AddValue("Error", "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description)
Call oBag.AddValue("CountOfJobsSucceeded", "0")
Call oAPI.Return(oBag)
oAPI.LogScriptEvent "PartitioningAndGroomingMonitor.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
strQuery = "SELECT Count(StatusCode) AS CountOfJobsSucceeded " & _
"FROM InternalJobHistory " & _
"WHERE CONVERT(varchar, TimeStarted, 101) BETWEEN " & _
"CONVERT(varchar, DATEADD(day, -" & strDaysLimitThreshold & ", GETUTCDATE()), 101) " & _
"AND CONVERT(varchar, GETUTCDATE(), 101) " & _
"AND Command = 'Exec dbo.p_GroomPartitionedObjects and dbo.p_Grooming' " & _
"AND StatusCode = 1 "
'Query DB
Set objRS = objCN.Execute(strQuery)
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
Set oBag = oAPI.CreatePropertyBag()
Call oBag.AddValue("Error", "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description)
Call oBag.AddValue("CountOfJobsSucceeded", "0")
Call oBag.AddValue("DaysLimitThreshold", strDaysLimitThreshold)
Call oAPI.Return(oBag)
oAPI.LogScriptEvent "PartitioningAndGroomingMonitor.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
Public Sub Save()
m_lNumber = Err.number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property