Set SQL Server Long Running Transaction Alert – Custom Query Alerts
Users need to set SQL server long running transaction alert which is one of the main reasons behind the log filling up. Also, this transaction makes the log remain active from the virtual log file containing the start of the transaction. Log truncation is restricted from that virtual file onward causing the log to grow in size & affect the complete operations of the database.
Hence, it is necessary to monitor long-running transactions. Evidently, on the page, we will be discussing techniques to set SQL server long running transaction alert. Sometimes such issues can be resolved by fixing page level corruption in SQL server & in some cases, special solution is required.
Table of Content
User Query for Long Running Transactions SQL Server
Now, before we move ahead, it’s quite important for us to understand what SQL users goes through. Therefore, we are going to have a look at the most asked queries regarding the same. This can help us find out the purpose of users for such events.
Now, that we know how a user get stuck in this situation, it’s time that we move ahead to provide them with a genuine solution for the same. Evidently, we have to first find out the problems, followed by the ways to detect & monitor them as well.
Problems with Long Running Transaction
Some of the issues the user will encounter due to SQL Server long running transactions are:
- This type of transaction cause many problems such as blocking or locking
- The presence of long running transactions on many servers fill up the transaction log
- It restricts the log truncation process that is needed to manage the log size
Ways to Detect SQL Server Long Running Transactions
Two possible methods to discover long running transactions are as follows:
-
- sys.dm_tran_database_transactions
The above stated dynamic management view (DMV) will return the information related to transactions at the database level.
In case of long running transaction, users need to execute SQL server monitor long running queries & some of the columns will be checked such as:
Starting time of the first log record [database_transaction_begin_time]
Current transaction state [database_transaction_state]
Log Sequence Number of beginning record [database_transaction_begin_lsn]
Note: For executing this command, DMV needs VIEW SERVER STATE permission on the server.
-
- DBCC OPENTRAN
The second alternative for this long running transactions SQL Server alert is to use a DBCC Command, which will help user in identifying the user ID of the transaction’s owner. It will help in tracking down the transaction source for a proper termination.
As this command will only give details of a long running transaction while executing them, we need to set up a SQL Agent job to run on every minute for proper monitoring. Though scheduling can be changed according to user requirements, job setup and repeated execution may lead to performance issues. However, if the scheduling is not frequent, there is high probability of missing long-running transaction.
Monitor SQL Server Long Running Transaction Alert
For using SQL Server Agent Alerts to monitor Long Running Transactions, following steps needs to be taken:
-
- Launch SQL Server Management Studio (SSMS) and click on SQL Server Agent
- Click on Jobs under SQL Server Agent section and select Alerts
- Right-click on Alerts and select ‘New Alert’
-
- A new window for creating alert will be shown where the detailed information needs to be entered such as:
- Name of the alert
- Type of the alert and choose ‘SQL Server performance Condition Alert’
- Click on OK
- A new window for creating alert will be shown where the detailed information needs to be entered such as:
-
- Fill the other details by selecting these options from the list.
- Set Object column as ‘SQL Server: Transactions’
- Select ‘Longest Transaction Running Time’ in Counter column
- Choose ‘rises above’ in alert option and value as 15 seconds
-
- Now click on ‘Response’ tab, create on ‘New operator’ to move ahead for SQL server long running transaction with ease.
-
- Now fill the details for creating the operator
-
- We will trigger an email to the operator, and execute a job in response of the alert.
-
- For creating a job, we will use the code defined below and call it from alert then, the result of this query will be sent in mail using sp_send_dbmail to resolve any issue.
-
- Customized message can be added in the additional notification message section in the email alert. For this, go to options, enter the message and delay time can be included between alerts responses.
-
- History section can be used while verifying the alert count
-
- Alert created can be disabled when it is not needed.
Handle SQL Transaction Log Issues with Tool
There are scenarios where users encounter the SQL Server long running transactions issues due to corruption or irregularities in the log files. Moreover, log files failures often brings more severe errors that may cause huge blunders. Therefore users must have a plan B. For example, a backup of MDF, NDF & LDF files or using the most advanced SQL Server Log Analyzer Tool from reputated brands. Therefore, we are mentioning the best tool below as well.
Conclusion
Finally, we have included some of the problems that can be caused by long-running transactions that affect the normal operations of the SQL Server database. Proper monitoring of the SQL Server Long Running Transaction is highly required. Along with two methods for the same purpose, another approach is to set SQL Server Long Running transaction alert. In addition, the solutions are also there for SQL server monitor long running queries to get desired results.
The SQL server alert can be created using the steps described above and it will be triggered when any type of transaction exceeds the threshold set by this alert. Since emails related to issue will be delivered to Database Administrator, actions will be taken.