SQL Server Performance

delete all the processes attached to a given DB

Discussion in 'Contribute Your SQL Server Scripts' started by dineshasanka, Dec 22, 2005.

  1. dineshasanka Moderator

    declare @PID as int

    DECLARE cur_Process_Id CURSOR FOR
    select P.spid from sysprocesses P ,sysdatabases D where P.dbid = D.dbid and D.name = 'myDBName'


    OPEN cur_Process_Id
    FETCH NEXT FROM cur_Process_Id into @PID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @PID
    EXEC ('KILL ' + @PID)
    FETCH NEXT FROM cur_Process_Id into @PID
    end
    CLOSE cur_Process_Id
    DEALLOCATE cur_Process_Id




    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
    dineshasanka, Dec 22, 2005
    #2

  2. Madhivanan Moderator

    Well. Those tables should be Master.dbo.sysprocesses and Master.dbo.sysdatabases

    Here is alternate approach that avoids Cursor

    declare @sql varchar(8000)
    set @sql=''

    select @sql=@sql+ ' Kill '+cast(P.spid as varchar(10)) from Master.dbo.sysprocesses P
    inner join Master.dbo.sysdatabases D on P.dbid = D.dbid
    where D.name = 'myDBName'

    Exec(@sql)



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Dec 23, 2005
    #2

  3. FrankKalis Moderator

    FrankKalis, Dec 23, 2005
    #2

  4. dirtydavey New Member

    How about this?

    declare @SQLText varchar(8000) ,@spid int

    select @spid = min(spid) from master..sysprocesses where dbid = db_id('Entities_Copy')

    while @spid is not null
    begin
    select @SQLText = 'kill '+convert(varchar(5),@spid)
    select @SQLText
    execute (@SQLText)
    -- inc counter
    select @spid = min(spid) from master..sysprocesses where dbid = db_id('Entities') and spid > @spid
    end
    dirtydavey, May 14, 2007
    #2

  5. Madhivanan Moderator

    What is 'Entities_Copy'?

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 14, 2007
    #2

  6. satya Moderator

    Like one of the previous threads this looks likes a proprietary code. I don't see much difference from Dinesh's code to this one.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, May 14, 2007
    #2

  7. dirtydavey New Member

    Its the same but does not you a curser.

    The entities should be your database name.
    dirtydavey, Jun 6, 2007
    #2

  8. Madhivanan Moderator

    quote:Originally posted by dirtydavey

    Its the same but does not you a curser.

    The entities should be your database name.
    Yes. You replaced cursor with While Loop.
    Refer the method I used

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jun 6, 2007
    #2

Share This Page