The above script will set the blocked process threshold to 10 seconds. In order to do this you’ll need to connect to the server in SQL Management Studio, open a new query window and use the following script.ĮXEC sp_configure 'show advanced options', 1 ĮXEC sp_configure 'blocked process threshold', 10 The first step in creating an Extended Event to monitor for database blocking is to set the blocked process threshold on whichever server you are wanting to monitor. If you’ve never worked with Extended Events then this can also serve as a basic tutorial on how to create your first EE. Though there are a plethora of options available within Extended Events, I’m only going to focus on what you will need to do in order to create a blocked process report. If you’re running SQL Server 2008 or later you have the ability to utilize Extended Events. I find this strategy very useful but it may not be the best option for all environments due to the need to enable xp_cmdshell.įortunately there is another low impact option available. The steps employed in that post utilize a combination of xp_cmdshell scripts and database mail to check for blocking and send out a blocking report. In a previous post I discussed how to Create A SQL Server Agent Job To Send An Alert When Database Blocking Occurs.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |