Monitoring For Database/Table Locks

02/12/2024 3:07 pm EST

In SQL Server Management Studio, go to the Activity Monitor

Machine generated alternative text: Microsoft SQL Server Management Studio File Edit View Debug Tools Window Help New Query Object Explorer Connect. JEFF-PC (SQL - Jeff-PC' Databases Security Server 0b

Right-click and lower the refresh interval as low as possible so you can see locks as close to real-time as possible.
Expand the Processes section. Click the arrow next to User Process Flag:

Machine generated alternative text: Processes Com Appl Wait Wait Wait Host„ JEFF-PC 24 JEFF-PC 6 JEF#PC JEF#PC 6 JEF#PC JEF#PC JEF#PC default defautt defauh defauh defauh defauh defauh Login Tay„ NT SER 1 Jeff-PO.I 1 Jeff-PCu T SER N SER NT SER NT SER Report Y tempdb master msdb msdb msdb Report S RUNNING SELECT Report Micro soft Micro soft SQ LAge SQ LAge SQ LAge Report S

Select (All).
Now you're going to need to click on either Blocked By or Head Blocker to sort by those first.

Machine generated alternative text: Processes Login Dat master SUSPEN SUSPEN SUSPEN SUSPEN SUSPEN SUSPEN SUSPEN SUSPEN SUSPEN UNKNO UNKNO UNKNO LOG WR LAZY W RECOVE XTP CK LOCK M SIGNAL Appl Wait Tim„ 49290 OOS PE 35010 ODS CL 7 SLEEP 56 LOG MG 707 73 DIRTY 152381 WAIT X 4046 REQUE 123290 KSOUR Wait Blocked By Head Blocker Host intema int ema intema intema intema intema intema intema intema 85 85

Once you see something show up under either, right-click on it and go to Details (or whatever is closest, I don't have a sample lock on my PC to show/doublecheck the exact wording). It's likely the lock is resolving either in the time users are taking to restart or because of the restart for some reason. The details are what we will need to look into the issue further.

Was this article helpful?