AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sql server detect deadlocks12/23/2023 ![]() ![]() In the next article, I am going to discuss how to read and understand the deadlock information that is logged in the SQL Server error log. To read the error log you need to use the sp_readerrorlog system stored procedure as shown below. The information about this deadlock now should have been logged in SQL Server Error Log. After a few seconds, you will notice that one of the transactions completes its execution successfully while the other one is chosen as the deadlock victim and rollback. From the first instance execute the spTransaction1 stored procedure and from the second instance execute the spTransaction2 stored procedure. Then open 2 instances of SQL Server Management Studio. UPDATE TableA SET Name = 'Anurag From Transaction 2'įirst, enable the Trace Flag by executing the following command UPDATE TableB SET Name = 'Priyanka From Transaction 2' UPDATE TableA SET Name = 'Anurag From Transaction 1' Now create two stored procedure as shown below. INSERT INTO TableB values (1002, 'Dewagan') INSERT INTO TableB values (1001, 'Priyanka') INSERT INTO TableA values (102, 'Mohanty') INSERT INTO TableA values (101, 'Anurag') Please use the below SQL Script to create and populate the tables with the test data. We are going to use the following two tables to understand this concept. Let us understand how to enable and use Trace Flag in SQL Server with an example. If we omit the -1 parameter then the trace flag will be set only at the session-level.ĭBCC Traceoff(1222, -1) Example: Trace Flag in SQL Server The -1 parameter indicates that the trace flag must be set at the global level. To enable the trace flag in SQL Server we need to use the DBCC command. ![]() Let discuss how to enable the Trace Flag in SQL Server. One of the options that is available in SQL Server is to use the SQL Server Trace Flag 1222 to log the deadlock information to the SQL Server Error Log. There are many ways available in SQL Server to track down the queries which are causing the deadlocks. How to Find Deadlock Queries in SQL Server? As we already discussed in our previous article when deadlocks occur, the SQL Server chooses one of the transactions as the deadlock victim and rollback that transaction so that the other transaction can move forward. Please read our previous article where we discussed Deadlock in SQL Server with Examples. how to log the deadlock information to the SQL Server error log. In this article, I am going to discuss Deadlock Logging in SQL Server Error Log i.e. Data Structures and Algorithms Tutorialsīack to: SQL Server Tutorial For Beginners and Professionals Deadlock Logging in SQL Server Error Log. ![]()
0 Comments
Read More
Leave a Reply. |