Once the deadlock occurs, the Profiler will display the relevant information in a graphical manner. And once the profiler is running, execute the code on Query Window 1 and later execute the code in Query Window 2. Start the profiler by clicking the ‘Run’ button. And in the ‘Trace Properties’ window, choose ‘ TSQL_Locks’ as the template.Īnd in the ‘Event Selection’ tab, please choose the following options:īefore you click on running the profiler, rollback the transaction which is running from the first example (the one which didn’t become the Deadlock Victim). Open the SQL Profiler and connect to the relevant SQL Server. However, for the sake of understanding, we will see how we can achieve this. And this isn’t the best way if you need to troubleshoot in a Production Environment. ** Please Note: Down-side of this method is you need to have the profiler running during the time that the deadlock had occurred. In such case (any case), we can use the SQL Profiler to detect the deadlock in a more user friendly manner. But things could get real hard during an actual situation where the processes are bit complex. In this example, it’s somewhat easier since we had chosen a simple situation. One of the issues we have in the aforementioned method is that we need to look into lots of textual information in order to extract the details which is relevant for the deadlock. Using SQL Profiler to capture a deadlock (Deadlock Graph) (I have highlighted the deadlock victim details in Red and the perpetrator details in blue.) After few seconds (30+), you will see a deadlock error message in Window 1.Īnd when you checked on the ERRORLOG file, you will be able to find out the relevant details which relate to the deadlock. BEGIN TRAN WAITFOR DELAY ' 00:00:10' UPDATE dbo.SampleDeadLock_2 SET Qty = 300 WHERE Item_Code = 3 UPDATE dbo.SampleDeadLock_1 SET Qty = 400 WHERE Item_Code = 1Īfterwards, execute the code in Window 1 and immediately execute the code in Window 2.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |