locked
Free Connections to a table before truncate RRS feed

  • Question

  • Hi All,

    I need to kill all connections to table before truncating table in production environment.Is there any script for the same?

    Regards

    Rahul

    Thursday, July 3, 2014 4:35 PM

Answers

  • You don't really have a connection to a table, but processes take out locks on rows in the table as they access the rows. Or on table if they scan many rows.

    It would be possible to query sys.dm_tran_locks to get the processes that hold locks, but you cannot really prevent new processes to come in.

    A more lazy method is to issue your "TRUNCATE TABLE" in one query window, and then run sp_who in another. You process will be reported as blocked by other process, so you kill that process. Then you run sp_who again, kill that guy and so on.

    Then again, killing processes in a production environment? It's not entirely appetising.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by SaravanaC Thursday, July 3, 2014 5:27 PM
    • Marked as answer by pituachMVP Monday, August 11, 2014 8:43 PM
    Thursday, July 3, 2014 5:16 PM

All replies

  • The TRUNCATE command will request a lock on the table.. that will prevent anything other than NOLOCK queries accessing it..

    What's the problem you're having?

    Thursday, July 3, 2014 5:05 PM
  • You don't really have a connection to a table, but processes take out locks on rows in the table as they access the rows. Or on table if they scan many rows.

    It would be possible to query sys.dm_tran_locks to get the processes that hold locks, but you cannot really prevent new processes to come in.

    A more lazy method is to issue your "TRUNCATE TABLE" in one query window, and then run sp_who in another. You process will be reported as blocked by other process, so you kill that process. Then you run sp_who again, kill that guy and so on.

    Then again, killing processes in a production environment? It's not entirely appetising.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by SaravanaC Thursday, July 3, 2014 5:27 PM
    • Marked as answer by pituachMVP Monday, August 11, 2014 8:43 PM
    Thursday, July 3, 2014 5:16 PM
  • Users are connected to a database and not the tables.  If your transaction hangs, you can find the user that is blocking you using sp_who2 and the kill command.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, July 3, 2014 5:23 PM
  • TRUNCATE is a DDL statement and requires a Sch-M lock on the object.  This is incompatible with all queries (even NOLOCK) which require a Sch-S lock.

    In SQL 2012+ your pending Sch-M lock will block new query requests and you will eventually acquire your Sch-M lock. 

    Before SQL 2012 new Sch-S requests from queries running in READ UNCOMMITTED or RCSI isolation levels would jump ahead of your pending Sch-M lock, and so on a busy system the DDL operation can take a long time or time out.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 3, 2014 5:40 PM
  • The Problem i am having is there alot of queries with (NOLOCK) option accessing the table.I want to kill only connection to that particulat table.Sql server version is 2005
    Friday, July 4, 2014 6:00 AM
  • Run this and paste the result to a query window. Have your TRUNCATE TABLE running in an other window as you are killing off processes.

    SELECT DISTINCT 'KILL ' + ltrim(str(request_session_id))
    FROM   sys.dm_tran_locks
    WHERE  resource_database_id = db_id()
      AND  resource_type = 'OBJECT'
      AND  resource_associated_entity_id = object_id('dbo.PMC_Communication_Queue_History_2')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 4, 2014 7:21 AM
  • Hi Erland

    Its not listing  correct session Ids when checked

    Friday, July 4, 2014 8:25 AM
  • Did you replace the table name? I forgot to put in a placeholder in place of the name I used when I tested.

    You need to be in the database where the table resides when you the query.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 4, 2014 9:01 AM
  • Hi Erland i did replace tablename in columnname  resource_associated_entity_id
    Friday, July 4, 2014 9:31 AM
  • OK. Then you need to supply more information about what you are seeing and what you expect to see. Else we cannot assist you further.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 4, 2014 7:41 PM
  • Hi Trehan11

    Is this still an issue?

    Thanks!


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, July 30, 2014 4:15 AM
  • Hi ALL,

    I got the permission to kill database connections in sp before truncate on off hous.Many thanks to all of you for your support.This thread can now be closed.

    Regards

    Rahul

    Sunday, August 3, 2014 10:54 AM
  • HI,

    This is your "job" to close the thread :-)

    Please mark all the answers and vote useful responses and this will close the thread (once you mark an answer the thread is treated as closed )


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Sunday, August 3, 2014 12:17 PM
    Sunday, August 3, 2014 12:17 PM