.
Its all about Database
CLOTA is a group of DBA experts. Our passion lead us here.
Jul 3, 2025
May 6, 2025
Monitoring SQL Server Deadlocks using Extended Events
Extended Events were first introduced in SQL Server 2008, and therefore you will not face any problem implementing them in our environment.
Extended Events is a lightweight performance monitoring
feature that enables users to collect data to monitor and troubleshoot
problems.
A deadlock is a situation
where two or more processes or threads are blocked indefinitely, waiting for
each other to release resources that they need to proceed.
Here we are going to observe how to gather deadlock information through Extended Events :
Creating SQL Server Extended Events to Capture Deadlocks using Management Studio
Step 1: First, open SQL Server Management
Studio (SSMS) and navigate to Management > Extended
Events > Sessions.
Step 2: Right-click on Sessions and select New Session.
Step 4: Go to the Events tab and in the event
library textbox type “deadlock” to filter deadlock events:
Step 5: We will select 2 events: Lock_Deadlock (Raised when a request to acquire a lock is cancelled for the victim of a deadlock) and Lock_deadlock_chain (Raised when a request to acquire a lock results in a deadlock. This event is raised for all members of the deadlock).
Step 6: Having both the events chosen, click on the configure button, and a new window will be opened, in this window we will take a snapshot of the sql_text field so that we can view the query that generated the deadlock
Step 7: In the Data Storage tab, select where you want
to store the Extended Event data use a file.
Step 8: After that, click on OK to save the Event configuration, we can see that the event is created and is already running.
Feb 5, 2025
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x0000 in file.
Error Message
Msg 823, Level 24, State 2, Procedure tables, Line 2 [Batch Start Line 2]
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read
at offset 0x00004675548000 in file '\\Server_Name\D2A_DB\MSSQL\Data\HISTORICAL_NDF_1.ndf'.
Additional messages in the SQL Server error log and operating system error log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many
factors; for more information, see SQL Server Books Online.
Cause
The Windows Session Timeout is 60 s by default. This timeout is dependent not only on Data Domain response time,
but also any network latency.
If there is a slow network or longer than expected Data Domain processing (such as rapid index expansion under
a heavy backup load), Windows resets the connection causing backup errors.
PS C:\Windows\system32> get-SmbClientConfiguration
Resolution
Increase the Windows Session Timeout to a higher value such as 180 s by running the following command:
PS C:\Windows\system32> Set-SmbClientConfiguration -SessionTimeout 180 -Force