![]() |
この記事は、現在の言語ではご利用いただけないため、英語で表示されます。 |
How to collect data for troubleshooting an SQL deadlock issue
Last Modified: 2022-03-18 20:36:42 Etc/GMT
Affected Products
Languages:
This article is available in the following languages:
常に適応し続ける XDR エコシステムが企業を活性化するしくみをお伝えします。
Trellix の CEO を務める Bryan Palma が、常に学習するセキュリティが決定的に必要であることを力説します。
Magic Quadrant で、19 のベンダーについてビジョンの完全性と実行能力が評価されました。レポートをダウンロードして詳細をご覧ください。
Gartner によると、XDR は脅威の防止、検出、応答を改善する可能性を秘めた新しい技術です。
2022 年に注意が必要なサイバー セキュリティ脅威は?
サイバー セキュリティ業界に安穏の時はありません。今こそ、この考え方を、ビジネスの活性化につながる利点として、また推進剤として念頭に置くべきです。
サイバー セキュリティの世界で信頼される二大リーダーが 1 つになって、耐久性の高いデジタル ワールドを実現します。
Trellix の CEO を務める Bryan Palma が、常に学習するセキュリティが決定的に必要であることを力説します。
As of May 14, 2024, Knowledge Base (KB) articles will only be published and updated in our new Trellix Thrive Knowledge space.
Log in to the Thrive Portal using your OKTA credentials and start searching the new space. Legacy KB IDs are indexed and you will be able to find them easily just by typing the legacy KB ID.
How to collect data for troubleshooting an SQL deadlock issue
Technical Articles ID:
KB93378
Last Modified: 2022-03-18 20:36:42 Etc/GMT Environment
ePolicy Orchestrator (ePO) 5.x
SummaryThis article describes how to collect data for troubleshooting SQL deadlocks. Solutions 1 and 2 describe two different methods on how to collect data for a deadlock issue. The methods either enable specific trace flags to collect the logs, or use the SQL Profiler trace tool. You only have to use one method.
Information about what a deadlock is, and how it differs from blocking. A deadlock occurs when two processes compete for exclusive access to a resource. But, one process is unable to obtain exclusive access to the resource because the other process prevents it. The result is a standoff, where neither process can continue. The only way out of a deadlock is for one of the processes to be closed. The SQL Server automatically detects when deadlocks occur, and kills one of the processes known as the victim. Blocking occurs when:
In the above situation, UserA can't access the object until UserB is finished with the object and has released it.
There’s a natural progression of events going on during a transaction. The transaction places locks on several resources because it needs to protect the referential integrity and database consistency. These locks block other transactions from acquiring locks on the same resources. The locks remain in place until the transaction with the lock is removed. The lock is released when either the transaction completes or is rolled back. These actions (locking and blocking) are normal events within the SQL Server and are to be expected. But, if you have a long running transaction, it can create long term blocking in the database, which prevents other work from going on. And, if two different transactions enter into a deadlock situation, SQL Server closes one of the transactions so that the system isn’t locked up. If you notice a few deadlocking errors, it’s unlikely to lead to any noticeable issues within the ePO console. These rare intermittent events can most likely be safely ignored. If many deadlock errors occur, collect additional information about the deadlock errors with the steps in this article. We can then investigate any problems further. Solution 1To capture deadlock data using the SQL Profiler Trace tool:
Solution 2
To capture deadlock data using trace flags, and SQL Error logs
After running the query, shows a table with Status, Global, and Session attributes. If these attributes are 0 for all columns, the flags are off, and need to be turned on. Use one of the following methods below to turn the trace flags ON. Method 1 - Turn on the trace flags without restarting the SQL Server:
NOTE: The SQL trace flags are disabled the next time SQL services are restarted.
Method 2 - SQL Deadlock traces to remain on, even when the SQL Server is restarted.
Add them to the startup parameters of the SQL Server:
To capture SQL Error Logs
Method 1 - Capture the SQL error logs from the SQL Server Management Studio console:
Method 2 - Locate the files on disk.
NOTE: The exact location of the SQL error logs is configurable and varies from one SQL instance to another. To locate the logs:
Related InformationAffected ProductsLanguages:This article is available in the following languages: |
|