April 13, 2003 at 1:58 pm
I am looking for a piece of code that will find all processes running against a given database and kill them versus having to kill them one by one in the enterprise manager.
The integrity checks part of my weekly maintenance plan is requiring the database be in single user mode.
BTW - the same plan on SQL 7 did not. Should I be worried about this??
The DB is 80gigs and the integrity checks take 50-60min a week.
The optimization part of the plan is running just fine.
Thanks
Randy
Edited by - notrub on 04/13/2003 3:28:50 PM
April 14, 2003 at 1:28 am
You can use the kill statement to kill the spid. The spids that are on a database can be obtained from sysprocesses table. You can loop through them and kill them one by one.
I have written a sample for you here. Note that this requires sysadmin permission.
The following sp call kills all users in TESTDB database
EXEC dbo.db_kill_all_spid 'TESTDB'
------------------------------------
The procedure is below. Before executing it make sure that you are aware of what this does.
DROP PROCEDURE dbo.db_kill_all_spid
GO
CREATE PROCEDURE dbo.db_kill_all_spid
(
@p_dbname VARCHAR(32)
)
AS
BEGIN
DECLARE @m_dbid INT
DECLARE @m_spid INT
DECLARE @m_sql NVARCHAR(255)
SELECT @m_dbid = dbid
FROM master..sysdatabases
WHERE name = @p_dbname
DECLARE curKillSet INSENSITIVE CURSOR
FOR SELECT spid
FROM master..sysprocesses
WHERE dbid = @m_dbid
OPEN curKillSet
FETCH NEXT
FROM curKillSet
INTO @m_spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @m_sql ="KILL " + CAST(@m_spid AS VARCHAR)
SELECT @m_sql
EXEC sp_executesql @m_sql
FETCH NEXT
FROM curKillSet
INTO @m_spid
END
CLOSE curKillSet
DEALLOCATE curKillSet
END
GO
April 14, 2003 at 3:36 pm
Like you said that the mmaint plan requires to be in single user mode. During the 50-60 min time do the users require to connect to the database. If yes then you need to get an outage for that period of time or schedule it at a time when there is minimal activity.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply