Blocking Process Monitoring and Auto Email Notification in SQL Server

From function perspective, to maintain a large scale business data warehouse is for making database system stable, robust and fast, which is a essential part to boost business team productivity and performance. However, for business users, the fundamental thing is data, so data can be delivered in high frequency and in time is the cornerstone for all business analysis and BI reporting. Obviously, the primary mandate for data management team is highly monitor ETL jobs to promise data process running well and smooth and never being blocked or corrupt by using process.

In this article, I am going to talk about how to build up a ETL job monitoring system to watch user query automatically in designed frequency. To accomplish this task, we need

1. Create a view to collect user query in real time
2. Create a stored procedure to detect blocking in different situations
3. Create another stored procedure to handle the notification email sending
4. Create a console script to overall control the workflow