If you are running SQL Server 2000, just issue an ALTER DATABASE
statement; it's quicker.

USE master
GO

ALTER DATABASE UserDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE UserDB
SET ONLINE

GO

Sincerely,


Anthony Thomas, MCDBA, MCSA



-----Original Message-----
From: Jake Smith
[mailto:mssqldba-ezmlmshield-x74810575.[Email address protected]
Sent: Friday, August 27, 2004 7:55 PM
To: LazyDBA Discussion
Subject: RE: Help with a script to KILL spids.

You can run this, replace the DBID with the correct ID from sysdatabases

/*************************************/
DECLARE SpidsToKill CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = 9

DECLARE @Spid int
DECLARE @SQL varchar(255)

OPEN SpidsToKill

FETCH NEXT FROM SpidsToKill
INTO @Spid

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @SQL = 'KILL ' + CONVERT( varchar(10), @Spid )

EXEC ( @SQL )

FETCH NEXT FROM SpidsToKill
INTO @Spid

END

CLOSE SpidsToKill
DEALLOCATE SpidsToKill

*/////////////////////////////////*
-----Original Message-----
From: Sanchez Anthony F
[mailto:mssqldba-ezmlmshield-x148120.[Email address protected]
Sent: Friday, August 27, 2004 5:47 PM
To: LazyDBA Discussion
Subject: Help with a script to KILL spids.

Hi,



I am attempting to create a script that will traverse the
master.dbo.sysprocesses table, to identify a table named "UserDB" then
kill any processes using this database. First I create a temp table,
then I use a while statement to run through the table identifying which
spid's to kill. For testing purposes, I commented out the Exec
(@Sqlstr) line.



Any suggestions on how to make this script work and possibly optimize it
would be greatly appreciated.



Thanks!



Anthony



Script:

------------------------------------------------------------------------
----------

CREATE TABLE #sys_processes_temp (

spid smallint, --The system process ID.

ecid smallint, --The execution context ID of
a given thread associated with a specific SPID.

status nchar(30), --The process status.

loginame nchar(128), --The login name associated with
the particular process.

hostname nchar(128), --The host or computer name for each
process.

dbname nchar(128), --The database used by the process.


cmd nchar(16)

)



DECLARE @sql VARCHAR(200)

SET @sql = 'select spid, ecid, status, loginame = rtrim(loginame),
hostname, dbname = case when dbid = 0 then null when dbid <> 0 then
db_name(dbid) end, cmd from master.dbo.sysprocesses'

INSERT INTO #sys_processes_temp

EXEC(@sql)

SELECT * FROM #sys_processes_temp



DECLARE @sqlstr VARCHAR(200)

WHILE exists (select spid from #sys_processes_temp where dbname =
'UserDB')

begin

DECLARE @spidno INT

select @spidno = spid from #sys_processes_temp

select @sqlstr = 'KILL ' + convert(varchar(10), @spidno)

print 'Killing spid ' + convert(varchar(10), @spidno)

-- Exec (@Sqlstr)

end



DROP TABLE #sys_processes_temp



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************

MS Sql Server LazyDBA home page