Lesson Learned #471: Why Can't I Expand the Database Table List from my Application?
Published Jan 08 2024 11:26 AM 1,315 Views

In the dynamic world of database management, we encounter unique challenges that impact the efficiency and performance of our applications. A common issue observed is the inability to expand the database table list during certain operations. This article addresses a specific situation where clients launch resource-intensive queries that take time and create locks, preventing other users from expanding the table list. 

 

For instance, we have a case where a customer, under a READ Committed transaction, is attempting to delete an index while needing to query the database for its tables and associated indexes.

 

What happens is a lock is generated because the second session is also in READ Committed and must wait for the first session to complete. In such scenarios, we can switch our isolation level to READ Uncommitted or use hints like ReadPast or NoLock to allow access.

 

However, there's a catch: the index might still appear in the list even if it no longer exists. This highlights the importance of data partitioning in large-volume databases. Moreover, remember that when using read-only replicas, executing this query will also replicate it to the replicas.

 

Finally, remember the usage ONLINE=ON in these type of operations: DROP INDEX (Transact-SQL) - SQL Server | Microsoft Learn

 

Session #1:

 

 

begin transaction 
drop INDEX Index2 on  Table1

 

 

Session #2:

 

 

begin transaction
select * from sys.indexes with (readpast)

 

 

Additional Information

 

Lesson Learned #319: Lock request time out period exceeded using SSMS - Microsoft Community Hub

'Lock request time out period exceeded' when connecting to the database from SSMS - Microsoft Commun...

 

Version history
Last update:
‎Jan 08 2024 11:26 AM
Updated by: