Get history of blocking

Question

Get history of blocking

jdc

Old Hand

Points: 367
More actions
July 18, 2019 at 4:55 pm

#3664240

Hi
Is there any view that stores blocking history?

Thanks

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 3

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;