none
SQL Alert setup RRS feed

  • Question

  • Hi DBA's,

    Please find the below script, we need to configure the alerts for running session only, the mentioned procedure running well but the query is fetch from entire session like (susspend,running status). please take and alter the procedure only for status like running session.

    Stores procedure:

    USE [master]
    GO

    /****** Object:  StoredProcedure [dbo].[longrunningsessions]    Script Date: ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



      
      
      
      
        
        
    ALTER procedure [dbo].[longrunningsessions]    
        
    as    
        
    create table #temp                        
    (                        
      [Session_id] [int],    
      [Login_name] [nvarchar](max),    
      [Host_name] [nvarchar](max),    
      [Sessioin_start_time] [nvarchar](max),                       
      [Session_status] [nvarchar](max),                        
      [Session_Duration]   [nvarchar](max),              
      [Session_query] [nvarchar] (max)    
                          
    )             
              
    insert into #temp          
              
    SELECT [Session_id]=spid,qs.loginame as Login_name,qs.hostname as Host_name ,            
    [Sessioin_start_time]=(SELECT start_time            
    FROM sys.dm_exec_requests            
    WHERE spid = session_id),            
    [Session_status]=Ltrim(Rtrim([status])),            
    [Session_Duration]=Datediff(mi, (SELECT start_time            
    FROM sys.dm_exec_requests            
    WHERE spid = session_id),            
    Getdate()            
    ),            
    [Session_query] = Substring (st.text, ( qs.stmt_start / 2 ) + 1,            
    ( ( CASE qs.stmt_end            
    WHEN -1            
    THEN            
    Datalength(st.text)            
    ELSE qs.stmt_end            
    END            
    -            
    qs.stmt_start ) / 2 ) +            
    1)          
    FROM sys.sysprocesses qs            
    CROSS apply sys.Dm_exec_sql_text(sql_handle) st          
              
              
    Declare @Body varchar(max),                        
          @TableHead varchar(max),                        
          @TableTail varchar(max),        
          @longrunningthreshold int          
    SET @longrunningthreshold=40                      
                            
                            
    Set @TableTail = '</table></body></html>';                        
    Set @TableHead = '<html><head>' +                        
                      '<style>' +                      
                      'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +                        
                      '</style>' +                        
    '</head>' +                        
                      '<body><table cellpadding=0 cellspacing=0 border=0>' +                        
                      '<tr bgcolor=#6CC7EE><td align=center><b>Session_id</b></td>' +     
                      '<td align=center><b>Login_name</b></td>' +    
                      '<td align=center><b>Host_name</b></td>' +                       
                      '<td align=center><b>Session_status</b></td>' +                        
                      '<td align=center><b>Session_Duration(Minute)</b></td>' +                  
                      '<td align=center><b>Session_query </b></td></tr>';          
              
              
              
    Select @Body = ( select                         
                            Session_id as[TD],    
                            Login_name as [TD],    
                            Host_name as [TD],                        
                            Session_status as[TD],                            
                            Session_Duration as[TD],                        
                            Session_query as[TD]                                              
                            from #temp where Session_Duration >= @longrunningthreshold                       
                     For XML raw('tr'), Elements)                        
                            
                            
                            
                             
    Set @Body = Replace(@Body, '_x0020_', space(1))                        
    Set @Body = Replace(@Body, '_x003D_', '=')                        
    Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')                        
    Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')                        
                            
    Select @Body = @TableHead + @Body + @TableTail                        
                            
    Select @Body         
          
    IF( @Body IS NOT NULL )             
    begin        
    EXEC msdb.dbo.sp_send_dbmail                        
    @profile_name = 'Mailserver',              
    @body = @Body,                        
    @body_format ='HTML',                        
    @recipients = N'EmailID',                         
    @subject = 'Long Running Session Details - Hostname';                        
    end                       
                            
    DROP TABLE #Temp   


    GO


    Thanks & Regards, PradeepAnand S SQL Database Administrator

    Monday, May 21, 2018 2:26 PM

Answers

All replies

  • I'd ask for help over here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.


    Monday, May 21, 2018 2:30 PM
    Moderator
  • Please alter the script share here

    Thanks & Regards, PradeepAnand S SQL Database Administrator

    Monday, May 21, 2018 3:54 PM
  • This is "where is" forum for direction on where best to ask questions. I'd ask for help in forums I linked.

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Monday, May 21, 2018 4:01 PM
    Moderator
  • Hi Dava,

    i was get the script from in the below mentioned link.

    http://www.sqlservergeeks.com/sql-server-configure-alerts-for-long-running-queries/

    please take and alter the procedure, for status like running session.



    Thanks & Regards, PradeepAnand S SQL Database Administrator

    Tuesday, May 22, 2018 1:00 PM
  • Hi Dava,

    i was get the script from in the below mentioned link.

    http://www.sqlservergeeks.com/sql-server-configure-alerts-for-long-running-queries/

    please take and alter the procedure, for status like running session.



    Thanks & Regards, PradeepAnand S SQL Database Administrator

    You've asked in "where is" forum for direction on where best to ask questions.

    I'd ask for help over here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.


    Tuesday, May 22, 2018 1:01 PM
    Moderator
  • PradeepAnand,

    You want your question to be answered by experts in the relevant technology, SQL Server in this case. If the site where you got the script provides for comments/discussion, you can ask for assistance from the script author there. Otherwise, ask your question in one of the forums Dave linked. You can copy and paste your question in a new post in the forum.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Tuesday, May 22, 2018 2:53 PM
    Moderator
  • PradeepAnand,

    You want your question to be answered by experts in the relevant technology, SQL Server in this case. If the site where you got the script provides for comments/discussion, you can ask for assistance from the script author there. Otherwise, ask your question in one of the forums Dave linked. You can copy and paste your question in a new post in the forum.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Hi Richard,

    Thanks for explanation, I have posted the same in the both the site.


    Thanks & Regards, PradeepAnand S SQL Database Administrator

    Tuesday, May 22, 2018 4:18 PM