This script is in it's infancy, but I wanted to share with you all now in hopes that someone could add some suggestions to improve it's results.
Occasionally our system will encounter some blocking and if we're lucky, we'll be close by or physically looking at the servers at the time which allows us to capture it. Other times it goes unnoticed by us, but not by our customers. My boss has always asked me how often we have blocking and to what severity. Most of the time I have no answer for him as we do not have any real time monitoring of blocking, thus no historical data to look back on either (we have used Teratrax Performance Monitor, but that does get expensive when you start putting it on multiple servers). This lead me to create my own type of real time monitoring of blocking which writes to a database for future reference. Eventually I will have it send me an email if the wait time on the first blocked process reaches a certain threshold. For now, here it is (simple isn't it).
The below script is put into a SQL Server job that is run once. The code will loop forever with a wait delay of 15 seconds between iterations.
while 1=1 begin
insert into tp_work.dbo.blockingsql select * from openquery
([linked server name here], 'select getdate(), text, owt.wait_duration_ms from sys.dm_exec_connections ec
cross apply sys.dm_exec_sql_text(most_recent_sql_handle)
cross apply sys.dm_os_waiting_tasks owt
where blocking_session_id = ec.session_id
and blocking_task_address is NULL ')
waitfor delay '00:00:15'
Basically, the script outputs the current date, the text of the process doing the blocking
not the blocked process, and the duration that the first process has been blocked for. Eventually I'd like to add a threshold for the duration of the blocking so that I'm not capturing 0.2 second blocks ect.
I have yet to decide if I'm going to put this job on the servers themselves or simply continue calling them via linked servers and storing the data on my admin server (suggestions welcome).
Hope someone is able to use this to their advantage