Feb 1, 2008

SQL server replicaion alert and restart fail jobs , vbscript

Are you a sql server dba ?
Your continue replication job failed lots of days and nobody tell you until wrong data cause business loss ? Using following script to help get alert and automatic restart jobs.
'1. Connect to sql server and get replication status
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True 'Windows authentication
oServer.Connect ""
Set queryResults = oServer.Replication.Distributor.EnumDistributionAgentViews

'2.
'Get
distribution agent name
queryResults.ColumnName(j) = "name"
agent_name =
queryResults.GetColumnString(i,j)

'3.
Get distribution agent status
queryResults.ColumnName(j) = "status"
queryResults.GetColumnLong(i, j) = 6

'***
Replication Task Status Constants

Constant Value Description

SQLDMOTask_Failed

6

At least one job failed to execute.

SQLDMOTask_Idle

4

All jobs are scheduled and idle.

SQLDMOTask_Pending

0

All jobs are waiting to start.

SQLDMOTask_Retry

5

At least one job is attempting to execute after a previous failure.

SQLDMOTask_Running

3

At least one job is executing.

SQLDMOTask_Starting

1

One or more jobs are starting.

SQLDMOTask_Succeeded

2

All jobs executed successfully.


'4. When agent status become failed , restart it .
Set oJob = oServer.JobServer.Jobs(agent_name)
oJob.Start

0 意見: