Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: T-SQL to kill SQL 7.0 PID's

    I am trying to run a script before a database restore (executed through a SQL Agent scheduled job) that will kill all PID's other than my own so I can get exclusive access and the restore will not fail.

    I got the following code snipit off the web...the source I cannot remember or I would give due credit... However, it does not seem to grap more than one PID. I am not very familiar with TSQL - can someone show me what should be added (for instance some sort of counter) so that the script will kill everyting but itself?

    Thanks in advance!

    Ryan Hunt

    Code:

    Begin
    declare @database sysname
    ,@Login sysname
    ,@Host sysname
    ,@OsUser sysname
    ,@SP_ID smallint
    ,@CRLF varchar(2)
    ,@SQL_Stmt nVarchar(500)
    ,@NotifyMsg nVarchar(500)

    declare cr_processes cursor for
    select spid,
    db_name(dbid) as DbName,
    loginame as Login,
    hostname as Host,
    nt_username as OSUser
    from master..sysprocesses where db_name(dbid) = (select top 1 db_name(dbid)
    from master..sysprocesses where spid=@@spid) and spid <> @@SPID and spid >=50

    -- close/kill all connections for this database
    open cr_processes
    while 1 = 1
    begin
    fetch cr_processes INTO @SP_ID, @database, @Login, @Host, @OSUser
    if @@FETCH_STATUS <> 0 break

    print 'Killing Process : ' + cast( @SP_ID as varchar(10))
    select @SQL_Stmt = N'kill ' + cast( @SP_ID as varchar(10))
    print @SQL_Stmt
    execute sp_executesql @SQL_Stmt
    end
    close cr_processes

    deallocate cr_processes
    End

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Will be this more simple?


    declare @sql varchar(8000)
    set @sql=''
    select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)+'go'+char(13)+char(10)
    from sysprocesses where spid<>@@SPID
    exec(@sql)

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Also, did you check this:

    alter database dbname set restricted_user with rollback immediate

  4. #4
    Join Date
    Aug 2003
    Posts
    39
    Yes, much. Is there anyway to run it in context to just one database. At the moment it will kill every PID other than mine that is on the server. I would like to kill all PID's other than mine attached to a specific database on the server.

    Thanks much!

    Ryan Hunt

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by AS400Admin
    Yes, much. Is there anyway to run it in context to just one database. At the moment it will kill every PID other than mine that is on the server. I would like to kill all PID's other than mine attached to a specific database on the server.

    Thanks much!

    Ryan Hunt
    Just add dbid and remove spid<>@@SPID

    declare @sql varchar(8000)
    set @sql=''
    select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)+'go'+char(13)+char(10)
    from sysprocesses where dbid=@your_db_id
    exec(@sql)

Posting Permissions

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