Issues with active spids and transitioning a database into Single User Mode
-
As a result of my desire to get SQL LiteSpeed to behave in the same way a SQL maintenance plan does, I have written multi step jobs to customize the way SQ LiteSpeed is called. Part of my monthly job is to do an integrity check, then a trans log checkpoint/backup, then shrink the database, then a full db backup, and file retention maintenance. I have encountered issues with transitioning the database into single user mode for the integrity check.
If an application has an active spid (with status 'runnable' and cmd 'select' in master.dbo.sysprocesses for example) and i call
set @su = 'ALTER DATABASE ' + @database + ' SET SINGLE_USER'
exec (@su)
the spid that is selecting goes into a blocking state and the spid trying to set the database into single user mode gets blocked, with both spids in turn halting. This causes the database to halt, and allows no more incoming connections. This causes EM to display the error "The database xxx in is transition.....")
I have investigated setting lock_timeout to a non infinite value, but that would result in the first spid winning rights to the lock. I need my integrity check to win. It is easy to cycle through master.dbo.sysprocesses and send a KILL to spids before I attempt an integrity check, but SQL Server documentation says NOT to send a kill processes where cmd = Select, Awaiting Command, Lock Monitor etc.
I am looking for a way to take an active spid and roll it back BEFORE I terminate it. Any Ideas?
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
This was removed by the editor as SPAM
-
Kill all the users out of the database before altering it into single user mode, here is a sample:
----------------------------------------------------------------------------------------------------------
--Kill all users out of a database--
--set the @dbname var to the database name in question--
----------------------------------------------------------------------------------------------------------
declare @spid varchar(3)
declare @sdbname varchar(255)
declare @cmd varchar(500)
declare @dbname varchar(255)
set @dbname = 'HCTEST'
--set this to the db you want to kill everyone out of
CREATE table #tmpUsers
(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30)
)
--kill all users
INSERT INTO #tmpUsers EXEC sp_who
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @sdbname
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
SET @cmd = 'KILL ' + @spid
EXEC (@cmd)
END
FETCH NEXT FROM LoginCursor INTO @spid, @sdbname
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
--end kill all users
drop table #tmpUsers
-
Thanks for the input Wesley. I have already built the mechanism to kill the spids as detailed in the code below. What I am really after now is a way to rollback an active transaction that does not have "RollBack Transaction" built into it's SQL. I am worried that I might kill a transaction in the middle of an update or insert, which could potentially be bad news. That is if I understand SQL Server correctly. Any way here's the code I am using. I am also raising a custom error for the NT application log.
--*****************************************
-- Declare database name to check
-- integrity of. Note, All indexes
-- will be repaired. Thus the
-- database is set to single
-- user mode, so all current
-- connections will be killed.
--*****************************************
declare
@database varchar(250),
@tablename varchar(250),
@su varchar(1500),
@mu varchar(1500),
@insert varchar(8000)
select @database = 'infolease' --Insert Database Name here
select @tablename = @database + '.dbo.sysusers'
select @insert = 'insert into #temp
select a.[spid], b.name as dbname, c.name as username, a.cmd, a.program_name as program ,a.hostname
from sysprocesses a, sysdatabases b, ' + @tablename + ' c ' + 'where a.dbid = b.dbid
and a.uid = c.uid
and b.name = ''' + @database + ''''
create table #temp(
[spid] int,
dbname varchar(250),
username varchar(250),
cmd varchar(250),
program varchar(250),
hostname varchar(250))
exec (@insert)
while (select count(*)from #temp) <> 0
begin
Declare
@spid int,
@spidtxt varchar(100),
@username varchar(250),
@hostname varchar(250),
@program varchar(250),
@cmd varchar(250),
@date varchar(50),
@dbname varchar(250),
@kill varchar(250)
select @spid = (select top 1 (spid) from #temp order by spid desc)
select @spidtxt = convert (varchar(100),@spid)
select @username = (select upper(username) from #temp where spid = @spid)
select @hostname = (select (rtrim(hostname)) from #temp where spid = @spid)
select @program = (select (upper(rtrim(program))) from #temp where spid = @spid)
select @cmd = (select (rtrim(cmd)) from #temp where spid = @spid)
select @date =
(select (convert (varchar(4),datepart (year,getdate ())) +
case when len(convert (varchar(4),datepart (month,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (month,getdate ())))
else
(select convert (varchar(4),datepart (month,getdate ())))
end +
case when len(convert (varchar(4),datepart (day,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (day,getdate ())))
else
(select convert (varchar(4),datepart (day,getdate ())))
end +
case when len(convert (varchar(4),datepart (hh,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (hh,getdate ())))
else
(select convert (varchar(4),datepart (hh,getdate ())))
end +
case when len(convert (varchar(4),datepart (mi,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (mi,getdate ())))
else
(select convert (varchar(4),datepart (mi,getdate ())))
end
 
)select @dbname = (upper(@database))
select @kill = 'kill ' + @spidtxt
Raiserror (60000,19,1,@spidtxt,@username,@hostname,@program,@cmd,@date,@dbname ) With Log
exec (@kill)
--print 'process ' + @spidtxt + ' killed.'
delete from #temp where spid = @spid
end
drop table #temp
/*
set @su = 'ALTER DATABASE ' + @database + ' SET SINGLE_USER'
set @mu = 'ALTER DATABASE ' + @database + ' SET MULTI_USER'
exec (@su)
dbcc checkdb (@database, repair_rebuild)
exec (@mu)
*/
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
you should enhance the script with "dbcc opentran" for each of the spids if you want to warn the spids involved, and retry those after a predefined timeout period but you should know that there should be NO potential bad news if you kill one in the middle because if it didn't commit it will be rolledback! that's ACID.
But still You should be doing this at off ours so there should be no active Transactions on the system (I hope) because a single user mode will step into everybodies toes anyway
HTH
* Noel -
"you should know that there should be NO potential bad news if you kill one in the middle because if it didn't commit it will be rolledback! that's ACID."
Ah, I see.
"But still You should be doing this at off ours so there should be no active Transactions on the system (I hope) because a single user mode will step into everybodies toes anyway "
That is a non-issue, this job will be run on scheduled maintenance weekends so users will expect the system to be down. I just built this for the case when a user goes home and locks their workstation with an application connected to the backend. If I execute dbcc checkdb during that condition, the database locks up.
Thanks for the info.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply