We are using SQL Server 2012 Enterprise edition.
Normally we get hardly any blocked processes, but last weekend we experienced very unusual situation. Within 2 hours we got more "blocked process" alerts than we did in the last year together. There were a few hundred alerts within this time. Then suddenly without any interference from anyone everything went back to norm and we didn't get any blocked processes ever since. I want to prevent this situation from occurring again.
I am well aware how to find what can be causing blocking at present, but I have very little idea how to find what caused the block in the past, which is currently resolved.
I checked error logs in SQL Server Management Studio, but there is nothing there under the date when blocking occurred. There is also nothing unusual in the Windows event viewer. Where else should I check?
Could you please help?
From what you describe, I'm not too sure you will actually find the cause of the previously blocking processes if you did not actively setup tracing i.e. have your blocked process threshold set and configured with an alert to provide said trace information. The situation you described is interesting and definitely worth monitoring.
Here is an article on blocked process threshold configuration in SQL Server and a link through to Alerts configuration.
Hope this helps