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

     

    sp_tables_info_rowset tblXXX

    sp_columns_rowset tblXXX

    sp_indexes_rowset tblXXX

    sp_check_constbytable_rowset tblXXX

    sp_provider_types_rowset tblXXX

    sp_table_statistics_rowset tblXXX

    sp_getschemalock tblXXX

    exec sp_prepexec @P1 output, NULL, N'SELECT "Col1601" FROM (SELECT "some dynamic generated query" FROM "dbname"."dbo"."tblName" WHERE somecriteria)

    sp_releaseschemalock tblXXX

    sp_releaseschemalock tblXXX

    sp_releaseschemalock tblXXX

     

    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.