Capturing deadlock events

Microsoft SQL Server

Traditionally, trace flags 1204 or 1222 are used to write information about deadlock events to the SQL Server log. But since SQL Server 2012, there is a default Extended Event session running called system_health that can be used instead. Extended events are stored as XML, and in this post I’ll show a method to extract information when a deadlock occurs and store it in a regular table. By having the deadlocks stored in a table, it is possible to view the history of deadlocks in order to enhance deadlock investigations.

Read more →

Automatic retries in a SQL Agent job

Microsoft SQL Server

Many SQL Agent jobs can be run automatically using the scheduler to run the job at specified times. But sometimes it would be better to run the job when specific criteria have been met, instead of at a specified time. For instance, when the data warehouse has finished loading.

This blog post will show how to create a job that starts and then waits / retries until given criteria are met.

Read more →