Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: disconnect a user whoz locking the DB with diff SPIDs

    I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
    The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
    how can i dosconnect a specific user from the DB.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if you think it's a big job to kill 1,000 spids, how big of a job is it to create them?

    I would want to know what process is soing that.

    Also, watch the ROLLBACK...it'll be twice as long as letting the work COMMIT

    But you've got bigger problems...

    ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Will blow everyone out, rollback all uncommitted work, and give you control
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •