Alerts

• ravisamigo

  SSCertifiable

  Points: 7440

  More actions

  March 24, 2011 at 2:52 am

  #234524

  Dear All,

  Please advise how to set the alerts on the below conditions.

  1. If SQL Services stopped.

  2. If Database goes offline\suspect mode.

  3. If RAM utilization increases more than 85%.

  4. If CPU utilization increases more than 85%.

  Thanks in advance.

  Regards,

  Ravi.

• Marco V

  SSCrazy

  Points: 2939

  More actions

  March 24, 2011 at 3:41 am

  #1302841

  ravisamigo (3/24/2011)

  ---

  Dear All,

  Please advise how to set the alerts on the below conditions.

  1. If SQL Services stopped.

  2. If Database goes offline\suspect mode.

  3. If RAM utilization increases more than 85%.

  4. If CPU utilization increases more than 85%.

  Thanks in advance.

  Regards,

  Ravi.

  Alerst like SQL Server alerts?

  In that case. 3 and 4 you can fix with creating 2 alerts of the type SQL Server performance condition alert. Give in the desired oject (RAM CPU PLE ect) and the counter condition

  For databases in suspect mode will be a severity being logged as event and if iam correct it is severity 22 en 23. Also on this you can make an alert of the typ SQL Server Event alert .

  Offline databases are no errors dont know how to fix this with alerts

  SQL server offline you cannot check with SQL server alerts as there is nothing to trigger the alert

• ravisamigo

  SSCertifiable

  Points: 7440

  More actions

  March 24, 2011 at 4:25 am

  #1302854

  Thanks.

  But I have tried to keep the database in suspect mode and set the alert severity to 23 but I couldn't get any alert.

  In event eveviewer, I can see serverity as 14 and 20 when database went to suspect mode

  Which objects I can choose for CPU and RAM theresh holds?

  Please advice.

  Thanks and Regards,

  Ravi

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  March 24, 2011 at 4:51 am

  #1302864

  ravisamigo (3/24/2011)

  ---

  Thanks.

  But I have tried to keep the database in suspect mode and set the alert severity to 23 but I couldn't get any alert.

  In event eveviewer, I can see serverity as 14 and 20 when database went to suspect mode

  Which objects I can choose for CPU and RAM theresh holds?

  Please advice.

  Thanks and Regards,

  Ravi

  I wouldn't recommend setting up a RAM alert. SQL Server generally takes up the memory available, so you're likely to see this value high in most instances. Getting alerts on it will just be noise. To set up a CPU alert you're going to need to create a WMI query and I'm not that familiar with those.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Marco V

  SSCrazy

  Points: 2939

  More actions

  March 24, 2011 at 8:46 am

  #1303016

  setup PLE en buffer hit ratio alerts, those will be of more use then you re mem usage

  See here the the code for creating the alerts

  DECLARE @sCounterObject varchar(32)

  DECLARE @PCstringvarchar(256)

  IF @@servicename = 'MSSQLSERVER'

  SELECT @sCounterObject = 'SQLServer'

  ELSE

  SELECT @sCounterObject = 'MSSQL$' + @@servicename

  --

  SELECT @PCstring = @sCounterObject + N':Buffer Manager|Page life expectancy||<|300'

  EXEC msdb.dbo.sp_add_alert @name=N'PC - Page life expectancy',

  @enabled=1,

  @delay_between_responses=600,

  @include_event_description_in=7,

  @performance_condition=@PCstring

  --

  SELECT @PCstring = @sCounterObject + N':Buffer Manager|Buffer cache hit ratio||<|0.95'

  EXEC msdb.dbo.sp_add_alert @name=N'PC - Buffer cache hit ratio',

  @enabled=1,

  @delay_between_responses=60,

  @include_event_description_in=7,

  @performance_condition=@PCstring

  GO