Post reply

Get history of blocking

  • July 18, 2019 at 4:55 pm

    #3664240

    Hi

    Is there any view that stores blocking history?

     

    Thanks

  • July 18, 2019 at 5:31 pm

    #3664257

    It's not stored in any of the DMVs. There is no reliable way to get that information unless you are monitoring, capturing it and saving it off somewhere. Some places do this with sp_WhoIsActive. You can find an example in this link:

    Logging Activity Using sp_WhoIsActive – Take 2

    Sue

  • July 19, 2019 at 4:48 pm

    #3664589

    Set up a blocking alert, when the alert fires off it should call SP_whoisactive. Dump the results into a temp table or Perm table and have it send you an email

  • July 19, 2019 at 4:55 pm

    #3664590

    something like this

    -----------------------------

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_update_alert @name=N'Blocked',

    @message_id=0,

    @severity=0,

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @database_name=N'',

    @notification_message=N'',

    @event_description_keyword=N'',

    @performance_condition=N'SQLServer:General Statistics|Processes blocked||>|1',

    @wmi_namespace=N'',

    @wmi_query=N'',

    @job_id=N'2656affe-1a04-482a-a1ac-51152174669a'

    GO

    ------------------------------------------------

    EXEC sp_WhoIsActive

    @find_block_leaders = 1,

    @output_column_list=

    '[start_time][sql_text][session_id][login_name][CPU][blocking_session_id][reads][writes][wait_info][open_tran_count][database_name][blocked_session_count]',

    @format_output = 0,

    @destination_table = 'WhoIsActive';

     

    EXEC msdb.dbo.sp_send_dbmail

    @recipients='ABC@ibm.com',

    @profile_name = 'MPAXCluster',

    @subject = 'Who Is Active',

    @body_format = 'TEXT',

    @query = 'SELECT TOP 1

    RIGHT(start_time,8)

    ,session_id

    ,right(login_name,20)

    ,RIGHT(CPU,20)

    ,blocking_session_id

    ,blocked_session_count

    ,RIGHT(wait_info,25)

    ,substring(sql_text,1,200)

    FROM [WSDBA].[dbo].[whoisactive]

    order by blocked_session_count desc',

    @execute_query_database = 'WSDBA',

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'WhoIsActive.txt',

    @query_result_header = 0,

    @query_result_width = 32767,

    @query_result_separator = '',

    @exclude_query_output = 0,

    @query_result_no_padding = 0,

    @query_no_truncate=1;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply