locked
SQL alert blocked processes RRS feed

  • Question

  • Dear all,

     

     

    I need to find a way of sending a text message preferably when any blocks occur in sql server. For example, is there a way that it could be set up in the Alerts section within SQL Server engine please?

     

     

     

    Thank you in advance!

    Thursday, August 14, 2014 2:26 PM

Answers

  • Your script is missing apostrophe characters. I assume it is the typo?

    You need to troubleshoot db mail in general.

    1. Is DB Mail configured? Would database mail send emails to the regular email address rather than SMS gateway?
    2. Would you receive text message when you send email to your tmomail address from regular email client? 

    There are also plenty of db mail related tables you can query to troubleshoot the issue. For example: http://msdn.microsoft.com/en-us/library/ms191278.aspx


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Tuesday, August 19, 2014 2:23 PM

All replies

  • Hello,

    You can configure Database Mail on SQL Server and create a SQL Server Agent job with the following code:



    select spid, blocked, waittype, waittime, lastwaittype, dbid, uid, cpu, physical_io, memusage, login_time, last_batch, hostname, program_name, nt_domain, nt_username, loginame from master..sysprocesses where blocked <> 0 and waittime > 60000 or spid in (select blocked from master..sysprocesses)

    IF @@ROWCouNT>= 1

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name= 'Database_Monitoring',

    @recipients= 'laurente.mary@yahoo.com',

    @subject= 'Database Blocking',

    @query= 'select spid, blocked, waittype, waittime, lastwaittype, dbid, uid, cpu, physical_io, memusage, login_time, last_batch, hostname, program_name, nt_domain, nt_username, loginame from master..sysprocesses where blocked <> 0 and waittime > 60000 or spid in (select blocked from master..sysprocesses)'

    END

    Source:

    http://www.sqlservercentral.com/Forums/Topic1359022-1292-1.aspx

    More resources:

    http://blog.extreme-advice.com/2012/09/25/find-blocking-in-sql-server-and-use-it-to-send-an-alert/

    http://databasebestpractices.com/how-to-setup-email-notification-when-blocking-occurs/

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by L_AY Friday, August 15, 2014 11:39 AM
    Thursday, August 14, 2014 6:47 PM
  • You can create event notification on "Blocked Process Report" event and use db mail and/or other methods in the activation stored procedure.

    Similar approach is discussed here: http://aboutsqlserver.com/2013/04/08/locking-in-microsoft-sql-server-part-16-monitoring-blocked-processes-report-with-event-notifications/


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Thursday, August 14, 2014 7:00 PM
  • Thank you for you reply! Is there a way of getting the notification as text please?
    Friday, August 15, 2014 10:38 AM
  • I do not think it is possible. However, most part of cell phone networks would allow you to use email/sms gateway - e.g. you send email with database mail to specific email address and it is delivered as text to your phone.

    Alternatively, you can code CLR stored procedure, or, as matter of fact, develop .Net service with external activation for SB Queue, which will utilize one of SMS gateways. 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Friday, August 15, 2014 2:28 PM
  • I tried using the following script to set up the SMS for the provider tmobile for example, but it still does not work,

    USE [msdb] GO EXEC msdb.dbo.sp_send_dbmail @recipients = ukmobilenumber@tmomail.net, @subject = "Test SMS", @body = "test sms" GO

    Would you please let me know what is going wrong?

    Thank you!

    Tuesday, August 19, 2014 11:20 AM
  • Your script is missing apostrophe characters. I assume it is the typo?

    You need to troubleshoot db mail in general.

    1. Is DB Mail configured? Would database mail send emails to the regular email address rather than SMS gateway?
    2. Would you receive text message when you send email to your tmomail address from regular email client? 

    There are also plenty of db mail related tables you can query to troubleshoot the issue. For example: http://msdn.microsoft.com/en-us/library/ms191278.aspx


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Tuesday, August 19, 2014 2:23 PM