August 22, 2003 at 9:11 am
Hi, All
I schedule DB restore job on our Web reporting server every night using a backup file from production server, more than half time, the job fails, I run detach user, drop DB and then restore DB, 3 steps in the job, I am only one currently using the Web reporting server, both production and Web reporting servers are Win2K with SP3 and SQL 2000 and sp3a installed. message I got at most time like this:
Executed as user: Domain\AltirisSvc. SPID 52: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds. [SQLSTATE 01000] (Message 6109) Process ID 52 is not an active process ID. [SQLSTATE 42000] (Error 6106). The step failed.
can anyone explan why the step failed, it say SPID 52 is not active process ID, why the job select this SPID 52 instead of other process ID that might be in active?
If I manually start the job (right click the job, then Click 'Start Job' the job start without problem! service account I use to create the job in Domain admin group and local admin group.
August 22, 2003 at 9:40 am
quote:
I run detach user, drop DB and then restore DB, 3 steps in the job
Can you post he scripts? Which steps were failed? You may run dbcc inputbuffer(52) to capture the last statement that was send to process 52. Any error messages in SQL Server errorlog?
August 22, 2003 at 9:46 am
here is 1st step of the job
declare @spid int
declare @spid2 char(2)
declare curs1 cursor
for
(select spid from sysprocesses where dbid= (select dbid from
sysdatabases where name = 'aexns'))
open curs1
fetch next from curs1 into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
set @spid2 = cast(@spid as char(2))
execute('kill ' + @spid2)
FETCH NEXT FROM curs1 INTO @spid
END
August 22, 2003 at 9:56 am
message from SQL logs:
Process ID 52 killed by hostname RLGHNCSXN1E, host process ID 1404.
I look Task manager:
Process ID 1404 is sqlagent.exe
the step failed is 1st one
August 22, 2003 at 9:57 am
Ok. It looks you want to disconnect all user's connections before droping the database.
You may try to replace your step1 with 'alter database' command with 'termination' option. See BOL for details.
Edited by - allen_cui on 08/22/2003 09:58:09 AM
Edited by - allen_cui on 08/22/2003 09:58:45 AM
August 22, 2003 at 10:42 am
just want to be sure, on 1st step of the job to run: ALTER DATABASE mydb WITH IMMEDIATE instead of detach all user
August 22, 2003 at 12:16 pm
For example.
use master
go
alter database northwind set offline with rollback immediate
go
drop database northwind
go
August 22, 2003 at 1:32 pm
Thanks, Allen
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply