Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Question Unanswered: How do you Kill All Processes in a Database?

    I want to schedule a DTS package that copies a database from one server to another. However, the copy fails if there are any active connections in the source database. I can MANUALLY go in and kill all each process in the database, but I want to automatically kill all of the processes in a TSQL statement. I know there is a KILL {spid | UOW} [WITH STATUSONLY] statement, but then how would you find each spid to kill?

    Isn't there an easy way to "kick out" all users who have a connection to the database without killing each process?

    Thanks...

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    declare @str varchar(8000)
    set @str=''
    select @str=@str+'kill '+cast(spid as varchar)+char(13)+char(10)+'go'++char(13)+char(10)
    from master..sysprocesses where dbid=id_your_db
    exec(@str)

    check if exists any alive process and repeat if needs...

  3. #3
    Join Date
    Aug 2003
    Posts
    13
    Thanks Snail! I actually was thinking along those lines as well...just thought there would be a system stored procedure for doing this. In any event, You can loop through all processes in a database and kill them like this where the database is named paentry:


    declare @name varchar(255)
    declare @id int
    declare @str varchar(255)

    declare spid_cursor cursor

    for

    SELECT master.dbo.sysdatabases.name,
    master.dbo.sysprocesses.spid

    FROM master.dbo.sysdatabases INNER JOIN
    master.dbo.sysprocesses ON master.dbo.sysdatabases.dbid = master.dbo.sysprocesses.dbid
    WHERE (master.dbo.sysdatabases.name = N'paentry')

    open spid_cursor

    fetch next from spid_cursor into @name,@id
    while @@fetch_status=0
    begin
    --print @id
    select @str = 'kill ' + cast(@id as varchar(6))
    print @str
    execute(@str)
    fetch next from spid_cursor into @name,@id
    end

    close spid_cursor
    deallocate spid_cursor

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suggest you put this in a stored procedure called spPissOffEndUsers().

    blindman

  5. #5
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    OR

    sp_F%^KYouALL Heheh .. make sure you eliminate your own spid from that select though

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I was only half joking. You run this process halfway through someone's lengthy UPDATE query and you could be in rollback for hours. I just wouldn't run a process like this to begin with. Clear the users out manually, or put the database in a restricted use mode.

    blindman

  7. #7
    Join Date
    Aug 2003
    Posts
    13
    Originally posted by blindman
    I was only half joking. You run this process halfway through someone's lengthy UPDATE query and you could be in rollback for hours. I just wouldn't run a process like this to begin with. Clear the users out manually, or put the database in a restricted use mode.

    blindman
    I need to automate the refreshing of a test database. I would run this at 3:00 in the morning where it is virtually impossible that anyone would even be actively doing anything in either the test or the production databases...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure it doesn't overlap any nightly processing or backups. Those are the big jobs that could screw up a database if they are interupted.

    blindman

  9. #9
    Join Date
    Aug 2003
    Posts
    13
    Originally posted by blindman
    Make sure it doesn't overlap any nightly processing or backups. Those are the big jobs that could screw up a database if they are interupted.

    blindman
    Thanks for your help! Appreciated.

Posting Permissions

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