Dealing with locked tables

  • Working with a sql2000 db that is used by a 3rd party app. This thing loves to lock tables which is OK, but I've recently caused a deadlock by trying to add a trigger to a table that was locked.

    When nothing happened after a minute or two, I canceled the script, but the user who was in the program had frozen up and the table was locked until the server could be rebooted.

    Is there a way to see which tables are currently being locked, and is there a management way to remove locks on a table? I have full access to the machine, its just a matter of knowing where to look.

    http://www.sqlservercentral.com/scripts/Miscellaneous/30085/ - Find open Transactions

    looked interesting, but even if these open transactions indicate a lock, it only lists at the db level, not the table.

    Bob

  • See http://vyaskn.tripod.com/sp_who3.htm for a good script that can be used to detect blocks (via EXEC sp_who_3 @run_mode = 'block' command)

    Microsoft aslo has help on this see http://support.microsoft.com/kb/271509.

    Use of SQL Server Profiler can help in pinpointing command or procs that are executing during the blocking or deadlocking.

    Francis

  • What app is this?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply