Locked MS SQL Server

If a SQL database becomes unresponsive, it’s possible that it’s either busy or has locking issues.

By default, executing a SELECT statement partially locks its tables. This lock prevents other statements from making certain changes to these tables until the SELECT execution ends. For instance, one cannot drop tables, or add/remove rows from tables involved in an executing SELECT statement. In normal conditions, locks will go away after the SELECT ends. Some locks will continue depending on how the SQL statements are written and the type of lock conflict. For instance, executing a DROP of a table locked by a SELECT might block the DROP process - the only way to unlock it is by killing the SELECT process or restarting SQL.

Is SQL Locked?

If a SQL database becomes unresponsive, it’s possible that it’s either busy or has locking issues.

If your SQL user has sysadmin or VIEW SERVER STATE rights, connect to your SQL instance via SSMS, right-click the SQL instance name → Activity Monitor → if the Overview bar shows (Paused), right-click on it and select Resume -> expand the Processes pane and you'll likely see rows of processes with information changing every few seconds. A row with Wait Type = LCK_M* indicates that this process is locked. See below for 3 examples. Sometimes, these locks are normal and will go away once the resources are unlocked. A locked row with a very large Wait Time (ms) might require a manual unlock (see next section).

Blocker Session ID and Details - The Culprit

On the Activity Monitor panel above, find the value on the “Blocked By” column - this is the Session ID that is caused the block, then replace that ID on the query below, then run the query on your SQL instance. This TSQL will show the details of the blocking query.

CODE

DECLARE @BlockingSPID int = 243   -- change this to any session ID
SELECT     
     r.wait_time / 1000                              AS wait_seconds
    ,s_blocker.login_name                            AS blocker_login
    ,s_blocker.host_name                             AS blocker_host
    ,SUBSTRING(t_blocker.text,(ISNULL(r_blocker.statement_start_offset, 0) / 2) + 1,
          ((CASE ISNULL(r_blocker.statement_end_offset, -1) WHEN -1 THEN DATALENGTH(t_blocker.text) ELSE r_blocker.statement_end_offset
          END - ISNULL(r_blocker.statement_start_offset, 0)) / 2) + 1)    AS blocker_current_statement
    ,t_blocker.text                                  AS blocker_full_sql
    ,r.blocking_session_id                           AS blocking_spid
    ,r.session_id                                    AS blocked_spid
    ,r.command                                       AS blocked_command
    ,DB_NAME(r.database_id)                          AS blocked_database
    ,s_blocked.login_name                            AS blocked_login
    ,s_blocked.host_name                             AS blocked_host
    ,s_blocked.program_name                          AS blocked_program
    ,s_blocked.status                                AS blocked_status
    ,r.wait_type                                     AS wait_type
    ,SUBSTRING(t_blocked.text,(r.statement_start_offset / 2) + 1,  ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(t_blocked.text)ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1)  AS blocked_statement
    ,s_blocker.program_name                          AS blocker_program
    ,s_blocker.status                                AS blocker_status
    ,s_blocker.last_request_start_time               AS blocker_request_start
    ,s_blocker.open_transaction_count                AS blocker_open_transactions
    ,r_blocker.command                               AS blocker_command
    ,r_blocker.wait_type                             AS blocker_wait_type
    ,DB_NAME(r_blocker.database_id)                  AS blocker_database
FROM sys.dm_exec_requests AS r
    INNER JOIN sys.dm_exec_sessions AS s_blocked
        ON  r.session_id = s_blocked.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t_blocked
    LEFT JOIN sys.dm_exec_sessions AS s_blocker
        ON  r.blocking_session_id = s_blocker.session_id LEFT JOIN sys.dm_exec_requests AS r_blocker
        ON  r.blocking_session_id = r_blocker.session_id  -- ── FIXED: use r_blocker.sql_handle only; blocker may be idle ───────
        OUTER APPLY sys.dm_exec_sql_text(
    ISNULL(r_blocker.sql_handle, 0x)
) AS t_blocker
WHERE r.blocking_session_id = @BlockingSPID
ORDER BY r.wait_time DESC

Unlocking SQL

Please try the approaches below in this order:

Terminate (kill) the reporting tools that are querying the data warehouse data (ex.: refreshing Tableau extracts, SSM queries, refreshing Power BI datasets, Excel reports).
Terminate your ETL+ running processes loading into the data warehouse.
On the Processes Panel above, right-click a row with Wait Type = LCK_M* -> Kill Process. Repeat this for all locked processes. If you have multiple databases on this list and only one with locking issues, use the panel’s Database header’s droplist to select the database that’s locked.
Caution: If you have access and sysadmin rights to the SQL instance, right-click the instance name → Restart and follow the wizard. This will reset all SQL connections and processes currently running.

Avoiding SQL Locks

The following tips can dramatically reduce the risk of SQL locks:

Schedule your table changes and loads so they don’t conflict with executing SELECTs. For Example:
- Avoid running complex SELECT statements in SQL Views and reporting tools, instead, convert them into SQL tables via ETL+.
- Use ETL+ Jobs to run reporting tool queries such as refreshing Tableau data sources, Power BI data sets, or Excel SELECTs after the feeding tables have been refreshed.
- If you’re running queries manually, avoid doing so when their tables are being modified. Monitor ETL+ Log to see if tables are being loaded now.
Consider adding the NOLOCK table hint to your SELECT statements or querying tools. Keep in mind that adding these table hints might affect the results of SELECT queries if their tables’ data changes during a SELECT execution. For more, search the internet for Dirty Reads and Phantom Reads with SQL Server. The following are examples of table hints:
- SELECT * FROM TableName1 WITH (NOLOCK)
- SSMS: Go to menu Tools → Options → Query Execution → SQL Server → Advanced → select the Transaction isolation level READ UNCOMMITTED.
- Tableau: When configuring the connection to a MS SQL Server data source, check the box for “Read uncommitted data”.
- Power BI: As noted above, manually add WITH (NOLOCK) to datasets' SELECT statements.
- Excel: It seems that there’s no way to configure this in Excel. Instead, add WITH (NOLOCK) at the MS SQL Server level (such as using a view to query the data and add the table hint).
Check if you’re using the latest ETL+ version.

More documentation about this topic:
https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16
https://www.sqlshack.com/locking-sql-server/

* Wait Types for locks: LCK_M_IS LCK_M_IU LCK_M_IX LCK_M_RIn_NL LCK_M_RIn_S LCK_M_RIn_U LCK_M_RIn_X LCK_M_RS_S LCK_M_RS_U LCK_M_RX_S LCK_M_RX_U LCK_M_RX_X LCK_M_S LCK_M_SCH_M LCK_M_SCH_S LCK_M_SIU LCK_M_SIX LCK_M_U LCK_M_UIX LCK_M_X

Keywords: dirty read, SQL Server dirty read concurrency problem, Transaction Isolation Levels,