Querying and Downloading Blocking and Deadlock Events
Baixar
Modo Foco
Tamanho da Fonte
TencentDB for SQL Server supports recording blocking events and deadlock events. Through these records, you can identify the detailed information of the SQL statements causing blocking and deadlock for optimization purposes.
This article describes how to query and download blocking events and deadlock events.
Note:
Only SQL Server 2012 Enterprise and later editions support blocking events and deadlock events. SQL Server 2008 R2 Enterprise does not support them.
The log time defaults to Beijing time. If the default time zone is modified, the storage time of log data will be displayed in the time zone of the instance server, while the console will show Beijing (UTC+8) time.
Effective October 26, 2026, the collection switch for blocking events and deadlock events in TencentDB for SQL Server is changed from disabled by default to enabled by default, to enhance system observability and alerting capabilities and improve troubleshooting efficiency. For details, see the announcement.
Prerequisites
Querying and Downloading blocking and deadlock events requires enabling the collection of blocking and deadlock events first.
Background
In a database, to maintain data consistency, the system does not release a specific resource when a session is modifying it and the modification is not yet complete. This prevents other concurrent sessions from accessing or modifying the same resource. When slow SQL queries or other exceptions occur, resources may be occupied for a long time, severely affecting access by other sessions and forming blocking events.
On the other hand, if multiple transactions compete for resources, a deadlock event occurs. For example, resource A held by the first transaction connection is not released, and the first transaction connection attempts to obtain resource B held by the second transaction connection. Meanwhile, resource B held by the second transaction connection is not released, and the second transaction connection attempts to obtain resource A held by the first transaction connection.
To address these issues, TencentDB for SQL Server provides a feature in the console for configuring the recording of blocking events and deadlock events, with collection enabled by default. This feature helps you quickly locate blocking and deadlock events in the database and identify the detailed SQL statements that caused the issues for optimization.
Related Information on Blocking and Deadlock Events
Collection for blocking events and deadlock events is enabled by default and cannot be disabled. The system collects blocking events and deadlock events, and the file records detailed SQL information that caused the blocking and deadlock.
The collection threshold for blocking events and deadlock events is 1,000 milliseconds by default (1,000 ms = 1 second). SQL statements that exceed 1,000 milliseconds are recorded as blocking SQL and deadlock SQL. You can customize the collection threshold (1,000 - 86,400,000 milliseconds).
A file is generated for blocking event and deadlock event log data every time the accumulated size reaches 10 MB. The maximum size of a single file is fixed at 10 MB. The time span for generating a single file is not fixed but depends on the speed at which logs are generated.
The retention period for blocking and deadlock events defaults to 7 days, after which they are automatically deleted.
Blocking and Deadlock Events File Generation and Download Policy
You can download blocking event or deadlock event files to view the corresponding log data and troubleshoot issues. Before downloading files, you can learn about the file generation and download policies described below to query and download files more efficiently.
When the blocking event and deadlock event log data of an instance accumulates to 10 MB, the system automatically splits the data and generates a new file. The file can be downloaded after its status becomes "Completed".
When the blocking event and deadlock event log data of an instance has not yet reached 10 MB, the status of the corresponding log file is "Writing". If the file already contains log data, you can download it from the corresponding log list in the console. The system generates the file immediately for download without waiting until it reaches 10 MB.
If the instance does not generate any blocking event or deadlock event log data, no log file will be generated and download is not supported.
Modifying Collection Thresholds for Blocking Events and Deadlock Events
Collection for blocking events and deadlock events is enabled by default for both existing and new instances and cannot be disabled. You can refer to the following steps to modify the collection threshold for blocking events and deadlock events.
1. Log in to the SQL Server console.
2. Select a region at the top, find the target instance, click Instance ID or click Manage in its Operation column to go to the instance management page.
3. On the instance management page, select the Operation Log tab.
4. Click Operation Log Settings.
5. In the pop-up window, modify the collection thresholds for blocking events and deadlock events (1000 ms - 86400000 ms), and click Save.
Querying and Downloading Blocking and Deadlock Events
1. On the instance management page, choose Operation Log > Blocking Event or Operation Log > Deadlock Event to view the corresponding event lists.
The supported fields include: File Name, Start Time, Last Updated Time, File Size, Operation (Download).
Logs can be searched based on time ranges: Last 5 minutes, Last 15 minutes, Last 30 minutes, Last 1 hour, Last 3 hours, Last 24 hours, Today, Yesterday, Last 3 days, Last 7 days, Last 30 days, and Custom time range.
2. In the Operation column, click Download to download the blocking event or deadlock event file.
Note:
When Download is clicked, the following scenarios may occur.
If the file status is "Completed", after
If the file status is "Writing", when the file has no log data, clicking Download will prompt "Log file is being prepared", and the download cannot be performed at this time. When the file has log data, you can copy the private network address and run the following command to download the file: wget -c 'file download address' -O 'custom file name'. Alternatively, you can download the file locally.
Related APIs
API | Description |
The interface (DescribeXEvents) is used to query the extended events list. | |
The interface (StartInstanceXEvent) is used to enable or disable extended events. |
Ajuda e Suporte
Esta página foi útil?
Você também pode entrar em contato com a Equipe de vendas ou Enviar um tíquete em caso de ajuda.
comentários