Monitoring Transactional Replication (with pull subscribers) ! he

Have a look at the Replication Alerts for some of this -
specifically the Replication: agent failure alert.
To help troubleshoot errors, the replication history
tables (MSsnapshot_history, MSlogreader_history etc) are
used, either directly or through the GUI. Also, setting
the agents to log history to a text file can be extremely
useful and give a more granular type of error handling
capability (see http://support.microsoft.com/?id=312292).
To backup the log just do a normal backup. The log won't
be truncated of this work until the transaction has
finished and the log-reader has read the whole
transaction.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
also have a look at the msrepl_errors table in the distribution database.
Errors are logged here.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3ce301c4e287$c466a4a0$a301280a@.phx.gbl...
> Have a look at the Replication Alerts for some of this -
> specifically the Replication: agent failure alert.
> To help troubleshoot errors, the replication history
> tables (MSsnapshot_history, MSlogreader_history etc) are
> used, either directly or through the GUI. Also, setting
> the agents to log history to a text file can be extremely
> useful and give a more granular type of error handling
> capability (see http://support.microsoft.com/?id=312292).
> To backup the log just do a normal backup. The log won't
> be truncated of this work until the transaction has
> finished and the log-reader has read the whole
> transaction.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Monitoring SQL Server with MOM

We have recently installed MOM and wish to customise the SQL Management Pack.
What are the top alerts (10-20) we should be looking to alert on?This very much depends on your requirements. We are monitoring the following
events:
1. severe SQL Server errors (severity 17 or higher)
2. a database becomes inaccessible (excluding the cases in which the
database is being restored or offline)
3. the SQL instance becomes inaccessible
4. any port binding failure
5. any backup failure
6. any SQL instance restarts
7. clustered SQL instance not running on the preferred node
8. clustered SQL instance failover
9. cluster node not up (mostly the one that doesn't have any SQL instance
running)
10. # of connections exceeding a threshold (had some rough apps not cleaning
up after itself)
11. Free space below a certain threshold
Linchi
"Court" wrote:
> We have recently installed MOM and wish to customise the SQL Management Pack.
> What are the top alerts (10-20) we should be looking to alert on?|||Many thanks for that.
"Linchi Shea" wrote:
> This very much depends on your requirements. We are monitoring the following
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleaning
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
> > We have recently installed MOM and wish to customise the SQL Management Pack.
> > What are the top alerts (10-20) we should be looking to alert on?|||Hi, is it possible for you to provide example event id's?
"Linchi Shea" wrote:
> This very much depends on your requirements. We are monitoring the following
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleaning
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
> > We have recently installed MOM and wish to customise the SQL Management Pack.
> > What are the top alerts (10-20) we should be looking to alert on?

Monitoring SQL Server with MOM

We have recently installed MOM and wish to customise the SQL Management Pack
.
What are the top alerts (10-20) we should be looking to alert on?This very much depends on your requirements. We are monitoring the following
events:
1. severe SQL Server errors (severity 17 or higher)
2. a database becomes inaccessible (excluding the cases in which the
database is being restored or offline)
3. the SQL instance becomes inaccessible
4. any port binding failure
5. any backup failure
6. any SQL instance restarts
7. clustered SQL instance not running on the preferred node
8. clustered SQL instance failover
9. cluster node not up (mostly the one that doesn't have any SQL instance
running)
10. # of connections exceeding a threshold (had some rough apps not cleaning
up after itself)
11. Free space below a certain threshold
Linchi
"Court" wrote:

> We have recently installed MOM and wish to customise the SQL Management Pa
ck.
> What are the top alerts (10-20) we should be looking to alert on?|||Many thanks for that.
"Linchi Shea" wrote:
[vbcol=seagreen]
> This very much depends on your requirements. We are monitoring the followi
ng
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleani
ng
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
>|||Hi, is it possible for you to provide example event id's?
"Linchi Shea" wrote:
[vbcol=seagreen]
> This very much depends on your requirements. We are monitoring the followi
ng
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleani
ng
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
>

monitoring sql error log for keywords

Hello,
We have Patrol monitoring running on our SQL Server 2000/2005 instances.
One of the alerts for Patrol is ErrorLogUpdates.
This sends out an alert when a particular keyword is found in the error log.
There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
I have been taking a look at quiet a few of our error logs and not making
any headway so far.
Does anyone have any suggestions as to what I can put in INCLUDE list and
EXCLUDE list of keywords to look for in SQL error log.
Thanks in advance.
Cheers.
Kunal.
Hi
Products like MOM come with preconfigured scripts, therefore I would expect
the software vendor to supply this.
John
"kunalap" wrote:

> Hello,
> We have Patrol monitoring running on our SQL Server 2000/2005 instances.
> One of the alerts for Patrol is ErrorLogUpdates.
> This sends out an alert when a particular keyword is found in the error log.
> There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
> I have been taking a look at quiet a few of our error logs and not making
> any headway so far.
> Does anyone have any suggestions as to what I can put in INCLUDE list and
> EXCLUDE list of keywords to look for in SQL error log.
> Thanks in advance.
> Cheers.
> Kunal.
|||First, you really have to determine what you want to be alerted on. For
instance, we monitor all the SQL errors with severity 17 or higher, backup
failure, port binding failure, restarts, and so on. Each of rthese error
types has a certain patterns that you can use. Unfortunately, MS has not been
very good at keeping the error messages consistent on many dimensions, making
life somewhat difficult.
Anyway, for port binding failure for instance, you can look for the
following text string; "SuperSocket Info: Bind failed on TCP port", and for
severe SQL errors, you may want to enumerate all the patterns like "Severity:
17", replacing 17 with 18, 19, 20, and so on. Patrol has a perl like language
and should be very easy to specific a regular expression pattern here.
Linchi
"kunalap" wrote:

> Hello,
> We have Patrol monitoring running on our SQL Server 2000/2005 instances.
> One of the alerts for Patrol is ErrorLogUpdates.
> This sends out an alert when a particular keyword is found in the error log.
> There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
> I have been taking a look at quiet a few of our error logs and not making
> any headway so far.
> Does anyone have any suggestions as to what I can put in INCLUDE list and
> EXCLUDE list of keywords to look for in SQL error log.
> Thanks in advance.
> Cheers.
> Kunal.

monitoring sql error log for keywords

Hello,
We have Patrol monitoring running on our SQL Server 2000/2005 instances.
One of the alerts for Patrol is ErrorLogUpdates.
This sends out an alert when a particular keyword is found in the error log.
There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
I have been taking a look at quiet a few of our error logs and not making
any headway so far.
Does anyone have any suggestions as to what I can put in INCLUDE list and
EXCLUDE list of keywords to look for in SQL error log.
Thanks in advance.
Cheers.
Kunal.Hi
Products like MOM come with preconfigured scripts, therefore I would expect
the software vendor to supply this.
John
"kunalap" wrote:
> Hello,
> We have Patrol monitoring running on our SQL Server 2000/2005 instances.
> One of the alerts for Patrol is ErrorLogUpdates.
> This sends out an alert when a particular keyword is found in the error log.
> There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
> I have been taking a look at quiet a few of our error logs and not making
> any headway so far.
> Does anyone have any suggestions as to what I can put in INCLUDE list and
> EXCLUDE list of keywords to look for in SQL error log.
> Thanks in advance.
> Cheers.
> Kunal.|||First, you really have to determine what you want to be alerted on. For
instance, we monitor all the SQL errors with severity 17 or higher, backup
failure, port binding failure, restarts, and so on. Each of rthese error
types has a certain patterns that you can use. Unfortunately, MS has not been
very good at keeping the error messages consistent on many dimensions, making
life somewhat difficult.
Anyway, for port binding failure for instance, you can look for the
following text string; "SuperSocket Info: Bind failed on TCP port", and for
severe SQL errors, you may want to enumerate all the patterns like "Severity:
17", replacing 17 with 18, 19, 20, and so on. Patrol has a perl like language
and should be very easy to specific a regular expression pattern here.
Linchi
"kunalap" wrote:
> Hello,
> We have Patrol monitoring running on our SQL Server 2000/2005 instances.
> One of the alerts for Patrol is ErrorLogUpdates.
> This sends out an alert when a particular keyword is found in the error log.
> There are two parameters I can specify, INCLUDE keywords and EXCLUDE keywords.
> I have been taking a look at quiet a few of our error logs and not making
> any headway so far.
> Does anyone have any suggestions as to what I can put in INCLUDE list and
> EXCLUDE list of keywords to look for in SQL error log.
> Thanks in advance.
> Cheers.
> Kunal.

Monitoring replication status via SNMP

I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help w
ould be GREATLY appreciated.
You can configure alerts to execute jobs. Use these jobs to raise SNMP alerts.
AFAIK there are no replication specific MIBs but there is a SQL Server specific one.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Derek Small" wrote:

> I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help
would be GREATLY appreciated.
>

Monitoring remaining space?

Is there a way to send e-mail alerts on the remaining space based on the
percentage space remaining?
I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
transactions & tempdb queries don't chew up the server resources/disk
space), and I want to monitor the data files for percentage use as well
before they grow. I was also thinking about using raw devices for the
tranlogs.
Thanx in advance,
/lYou can get some stuff from www.dbmaint.com (free utilities). There are two stored procedures that
can be of interest. One warn you if there is less then n% free space. The other one warn if you had
autogrow. You might need to modify them to suit your needs.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||Thanx!!!|||Is probably overkill for you requirements but we use MOM to do just that...
--
HTH
Ryan Waight, MCDBA, MCSE
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||> I was also thinking about using raw devices for the tranlogs.
In general, that is not advisable. NTFS files are must easier to manage.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>

Monitoring agent status

All -
I'm trying to setup a way to monitor the agent status for replication. I
tried using the built in "Alerts" and they are not working. I made sure that
they are enabled, but after enabling the Alerts, it does not show that the
event had ever happened when I check the history. I've made sure that the
alerts are enabled on the distributor and no where else.
So my next question would be is there a way programatically monitor agents?
I see the system view sys.dm_qn_subscriptions, but when I select * from it,
nothing is returned.
I am trying to monitor the failures and retries.
"Paul Ibison" wrote:

> Which event re you monitoring. I recall from another poster that we couldn't
> get the on success alert running as the event couldn't get raised to the
> windows log (couldn't change the message attributes, unlike in SQL 2000) ,
> but I am not aware of any others.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
on, but for now as a stop-gap you could use a notification on the job itself.
Cheers,
Paul
|||We are using 2005. I'll try and give the notification on the specific job a
try and see if that works.
"Paul Ibison" wrote:

> This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
> on, but for now as a stop-gap you could use a notification on the job itself.
> Cheers,
> Paul
>