Lesson Learned #471: Why Can’t I Expand the Database Table List from my Application?

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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 Community Hub


Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.