Home Forums SQL Server 2008 T-SQL (SS2K8) Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries RE: Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries

  • Possible, yes. Whether it's a good idea or not is another matter.

    What happens if that's a long-running data import. Job automatically kills it after 2 hours, job rolls back for the next maybe 3 hours, net result several wasted hours and a data import that needs to be rerun and when it is, it'll be killed after 2 hours, etc, etc.

    Better idea, identify long running queries that cause blocking and tune them so that they aren't so long running and don't cause so much blocking. Fix the cause, don't patch the symptoms.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass