Comments
Sort by recent activity
@Eddie D and others, I don't think that blocking issue is fixed. Or it's regressed and appeared again. I just experienced it on an Azure Managed Instance today. SQL Search window wasn't even active. I hadn't been using it for quite some time. I was working in another window and hit a random schema block that was causing me to not be able to create a view. I ran sp_whoisactive and found a SPID from myself... but no active query windows or connections that matched the SPID.
I let it go for about 6 minutes while my query was blocked. Eventually I started closing tabs in SSMS and viola--SQL Search was the blocker.
Not only was it blocking ME and one of our developers, it was also blocking some background "dmvcollector" query, which I assume is being run by someone/something at Microsoft.
Anyway, long story short, version 3.5.5.2703 of Redgate SQL Search can cause schema blocks (LCK_M_S) in Azure. Looks like the SQL Search window can become frozen in some state where the query doesn't complete.
This is the blocking query: SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name], tbl.object_id AS [ID] FROM sys.tables AS tbl ORDER BY [Schema] ASC,[Name] ASC
/ comments
@Eddie D and others, I don't think that blocking issue is fixed. Or it's regressed and appeared again.I just experienced it on an Azure Managed Instance today. SQL Search window wasn't even active...