Does anyone have documentation or know what sp_releaseschemalock does?
I had an issue that occurred last week when I was on vaca. The above proc was blocking for over 24 hours being called thru linked server. All my logs are gone so I do not know who or what. All I have is he was the head of the chain..
The application experienced issues during this window and my boss wanted me to se if I saw anything.
The blocking continued until sql was restarted.
It is sql2000 sp4.
I've seen this happen where there was a linked server query running and killed. However the connection persisted and was reporting it was at a 100 percent in the rollback state, but was blocking other queries. I simply stopped / started DTC on the linked server and then the connection cleared. In my several cases, this has always worked for me without having to stop/start sqlserver.
You should check your linked server configurations and make sure they are "collation compatible" checked. Otherwise the entire remote table is returned to the local calling server to execute the WHERE clause.
In regards to sp_releaseschemalock ....below is part of a rootcause analysis I had to give one time before. I've edited it quite a bit to rip out tablenames/queries we use. I used profiler to capture these sprocs.
This is one of many sp's which are called as a result of a linkedserver query.
exec sp_prepexec @P1 output, NULL, N'SELECT "Col1601" FROM (SELECT "some dynamic generated query" FROM "dbname"."dbo"."tblName" WHERE somecriteria)
All of this overhead generates a lot of disk IO's and stacks up on the blocking.
All of this occurs because the SQL Server on serverA has no statistical information about the tables involved in the linked query. ServerB will always inquire that information for each call.
Hope this helps out some.