October 30, 2009 at 8:45 am
I am running some SMO Transfer Database tasks through SSIS. The job is automated. Nothing important is running on the db at the time but the job will fail if ANYONE has an old session open on that database.
Has anyone got a quick script that will look up the spid's of sessions for a database and then run them through a KILL process?
Just time saving.
Many thanks,
Shark
October 30, 2009 at 8:51 am
use master
alter database [MyDatabase] set offline with rollback immediate
alter database [MyDatabase] set online
October 30, 2009 at 9:04 am
Execute this.
DECLARE
@p_SPID int,
@p_SQL nvarchar(2000)
DECLARE #cur_Processes CURSOR FOR
SELECT
p.SPID
----------> AND spid >= 51 (because spid's of 50 or less are reserved for internal use.)
FROM
master.dbo.sysprocesses AS p
JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid )
WHERE
d.Name = '<Database Name, , Name>' and spid >= 51 -- since spid's of 50 or less are reserved for internal use
OPEN #cur_Processes
FETCH NEXT FROM #cur_Processes INTO @p_SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID )
PRINT @p_SQL
EXECUTE( @p_SQL )
FETCH NEXT FROM #cur_Processes INTO @p_SPID
END
CLOSE #cur_Processes
DEALLOCATE #cur_Processes
SQL DBA.
October 30, 2009 at 9:09 am
Both of those options are great. Thanks guys
October 30, 2009 at 9:11 am
USE [master]
GO
ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DB_NAME] SET SINGLE_USER
GO
ALTER DATABASE [DB_NAME] SET MULTI_USER
-Vikas Bindra
October 30, 2009 at 9:13 am
i use this and stick it in master:
usage is simply sp_kill databasename
this is simliar to Sanjay's, but a little more robust on a couple of validation things like your own spid, missing databaser name, etc.
--enhanced 02/04/2005 to also list hostname
CREATE PROCEDURE sp_Kill
@DBNAME VARCHAR(30)
--Stored procedure to Delete SQL Process
AS
BEGIN
SET NOCOUNT ON
DECLARE @SPID INT
DECLARE @STR NVARCHAR(50)
DECLARE @HOSTNAME NVARCHAR(50)
DECLARE @DBID INT
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100) ,
RID INT,
)
select @DBID=db_id(@DBNAME)
IF @DBID IS NULL
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
INSERT INTO #TMPLOG EXEC SP_WHO
--do not even try to kill spids that are SQL server itself.
DELETE FROM #TMPLOG WHERE SPID < 50
IF @@ERROR <> 0 GOTO Error_Handle
DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG
WHERE DBNAME LIKE @DBNAME
OPEN CURPROCESSID
FETCH NEXT FROM CURPROCESSID INTO @SPID
SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID
IF @SPID IS NOT NULL
PRINT 'Spid Process Kill List For database: ' + @dbName
ELSE
PRINT 'NO Processes Exist to be killed on database ' + @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SPID = @@SPID
BEGIN
PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
END
ELSE
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
END
IF @@ERROR <> 0 GOTO ERROR_HANDLE
FETCH NEXT FROM CURPROCESSID INTO @SPID
END
Error_Handle:
IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
drop table #tmpLog
SET NOCOUNT OFF
END
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply