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).

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,