Putting a database in single user mode in script ?
gary ng
I tried to put a database in single user mode using the stored proc
sp_dboption. But before I can do that I need to kill all active process on
that database. So I loop through them. Unfortunately, it seems to loop
endlessly. Are there any other options to do what I want. Below is the
script I used :
===============
drop procedure SingleUserdb
go
create procedure SingleUserdb
@DbName varchar(50)
/* WITH ENCRYPTION */
AS
DECLARE @finish char(1)
,@numProcess int
,@spid smallint
,@status nchar(30)
,@sqlKill varchar(100)
,@dbid smallint
,@loopCount smallint
SELECT @dbid = db_id(@DbName)
,@loopCount = 1
-- Loop until the only process left is this process itself.
SELECT @finish = 'N'
WHILE @finish = 'N'
BEGIN
SELECT @numProcess = 0
DECLARE spid_cursor CURSOR FOR
SELECT spid,status FROM master.dbo.sysprocesses
WHERE hostname IS NOT NULL AND dbid = @dbid AND spid <> @@SPID
FOR READ ONLY
OPEN spid_cursor
FETCH spid_cursor INTO @spid, @status
WHILE @@sqlStatus = 0 AND @loopCount < 100
BEGIN
Select @loopCount = @loopCount + 1
SELECT @sqlKill = 'KILL ' + CONVERT(varchar(10),@spid)
IF RTRIM(@status) <> 'runnable' EXEC (@sqlKill)
SELECT @numProcess = @numProcess + 1
FETCH spid_cursor INTO @spid, @status
END
CLOSE spid_cursor
DEALLOCATE CURSOR spid_cursor
IF @numProcess = 1 SELECT @finish = 'Y'
END
EXEC master.dbo.sp_dboption @DbName, 'single user', 'TRUE'
RETURN 0
=============================================
thanks for any help in advance.
regards,
gary
Bret Halford
gary ng wrote:
Are there typically more than 100 users in the database? It looks like the
direct issue with the code may be that you don't reset @loopCount to 1
when you enter the inner loop for a second time.
However, on the whole, the code looks overly complicated. I think something
much simpler will do the job, on the lines of... (note: I haven't actually
tested
this exact script).
Note, however, that this procedure may not work at all times. A database may
be in use
by a spid (preventing sp_dboption "single") without being listed as "using"
the database in sysprocesses - for instance, they may have "used" another
database and be doing a cross-database transaction involving your database,
thus increasing it's keep count and preventing single user mode. So you will
want to check for success of the sp_dboption and take further action if
the db did ot get set to single user mode.
Cheers,
-bret
drop procedure SingleUserdb
go
create procedure SingleUserdb
@DbName varchar(50)
/* WITH ENCRYPTION */
AS
DECLARE
,@spid int
,@sqlKill varchar(100)
,@dbid smallint
SELECT @dbid = db_id(@DbName)
SELECT @spid = spid FROM master.dbo.sysprocesses where spid <> @@spid and dbid
= @dbid
while (@@rowcount > 0)
BEGIN
SELECT @sqlKill = 'KILL ' + CONVERT(varchar(10),@spid)
SELECT @spid = spid FROM master.dbo.sysprocesses where spid <> @@spid and
dbid = @dbid
gary ng
either my old script or the modified one as suggested just don't get out of
the loop. Then I changed it to the following :
==================================================================================
DECLARE
@spid int
,@sqlKill varchar(100)
,@dbid smallint
,@loopCount smallint
,@DbName varchar(50)
SELECT @DbName = 'DataCmon'
SELECT @dbid = db_id(@DbName)
SELECT @spid = min(spid) FROM master.dbo.sysprocesses WHERE
spid <> @@spid AND dbid = @dbid
while (@spid > 0 )
BEGIN
SELECT @sqlKill = 'KILL ' + CONVERT(varchar(10),@spid)
select @sqlKill
exec (@sqlKill )
SELECT @spid = min(spid)
FROM master.dbo.sysprocesses
WHERE spid <> @@spid AND dbid = @dbid and spid > @spid
END
EXEC master.dbo.sp_dboption @DbName, 'single user', 'TRUE'
-----------------------
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
Database option 'single user' turned ON for database 'DataCmon'.
Running CHECKPOINT on database 'DataCmon' for option 'single user' to take
effect.
Server Message: Number 3508, Severity 10
Server 'DEVSYBASE', Procedure 'sp_dboption', Line 684:
Attempt to set 'DataCmon' database to single user mode failed because the
usage count is 2. Make sure that no other users are currently using this
database and rerun CHECKPOINT.
(1 row affected)
(1 row affected)
(return status = 0)
=======================================================================================
As you can see, the loop ends but the single user option failed. I don't
know where does that usage count 2 is from as this is the only process using
it.
"Bret Halford" <br...@sybase.com> wrote in message
news:4221F8AB...@sybase.com...
Bret Halford
gary ng wrote:
> As you can see, the loop ends but the single user option failed. I don't
> know where does that usage count 2 is from as this is the only process using
> it.
>
There are two main possibilities. One is, as I mentioned before,
that some spid that is officially "in" some other database is involved
in a cross-database transaction with this database. You might be
able to see this by checking syslocks for spids that are holding
locks in this database.
The second possibility is that some bug in ASE caused the keep count
to not get decremented when a process was killed for some reason.
There is little you can do about this case aside from rebooting ASE
to force the database to go through a clean recovery cycle
to reset the keep count.
Cheers,
-bret