SQL Server Performance

Lock in SQL when deleting table by DTS

Discussion in 'SQL Server DTS-Related Questions' started by JCBailly, Nov 18, 2003.

  1. JCBailly New Member

    Hello everybody.
    First of all, i am french so please forgive my english [:I]

    Does anybody know how I can delete a SQL table by DTS if someone is locking the table.
    I have to do that because we use DTS to replicate the database from AS400 in SQL. So we drop tables ,create them and fill them from the AS400 one by one with DTS.
    But sometimes, some users stay connected to SQL and the DTS blocked on the table and stay block until the morning.

    Anybody got an idea ?

    Thanks for your help.

  2. ChrisFretwell New Member

    You can use assorted methods to find out who is connected to a table and then kill the connection. Once its killed, you will be able to drop the table.

    You can either kill all non-system processes (even if they are not locking a specific table), or go a little bit further and only kill sessions locking tables you need.
    Sp_who/sp_who2 will quickly return who is connected. Sp_lock will return who is locking what and how. You can check the results of either in dts and issue the appropriate kill statement.

    There are some user written sprocs out there on the web with detailed examples. I might be able to find a couple if I search through my links.

  3. satya Moderator

    Refer to this LINKhttp://www.sql-server-performance.com/q&a37.asp for relevance of script to kill the users connected/idle.

    And other than this I think there is no issue on scheduling the DTS package for database update.

    Satya SKJ
  4. JCBailly New Member

    Perfect, just what I need [<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br />Thanks you very much for your help.<br /><br />JCB

Share This Page