Home » Microsoft Operations Manager 2005 » Monitor recovery mode

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Microsoft infrastructure and system management. I am a principal engineer in the FastTrack for Azure team, part of Azure CXP, at Microsoft. Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP
MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
OMS
Inside the Microsoft Operations Management Suite

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.



MVP awarded 2007,2008,2009,2010

My Books

Service Manager Unleashed


Orchestrator 2012 Unleashed


Inside the Microsoft Operations Management Suite

Monitor recovery mode

This script will check recovery mode of a MS SQL database. Its pretty simple to modify to check other databases or all databases. This example will check if the Onepoint database is running with SIMPLE recovery mode. If it does, a local event will be generated. You can then create a event rule to collect that event and generate an alert in MOM.

set cn = Nothing
set rs = nothing
strSQLServer = "localhost"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=" & strSQLServer & ""
strSQLQuery = "select name, databasepropertyex(name, 'recovery') as RECOVERY from sysdatabases WHERE NAME = 'OnePoint'"

Set rs = cn.execute(strSQLQuery)
strDBName = rs("Name")
strMode = rs("Recovery")

If strMode = "SIMPLE" then
 Const EVENT_WARNING = 2
 Set objShell = CreateObject("Wscript.Shell")
 objShell.LogEvent 2, "The " & strDBName & " database is running in simple recovery mode"
End If

  1. Create a new script and paste this source
  2. Create a rule that run the script every X minute
  3. Create a rule that collects and generate alerts on event ID 2 from WSH in the application log.