SQL Job stopping

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Liz (Liz_at_discussions.microsoft.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 07:09:04 -0700

Windows 2000 latest sp
SQL 2000 sp3

I've got 2 seperate jobs; Job1 and Job2 that run every 5 minutes on Server1
and Server2, respectively. Each job executes one stored procedure. On
Server1\job1 everything works fine but Job2 on Server2 stops at 430am
everyday. That's also the time when my sproc is suppose to start log
restores. I've tried to delete the job and re-create it to no avail. The job
doesn't fail, it just stops. Remember it works fine on Server1. I've captured
the following from the step log file:
-----------------------------
Job 'SLS Production L' : Step 1, 'exec GenesisBackups' : Began Executing
2004-10-22 04:30:00 - this is the last successful step that ran
--This is where it stops.
Msg 0, Sev 0: Operation canceled [SQLSTATE HY008]

Query cancelled.
------------------------------
Following is the part of the stored procedure that's suppose to execute
during this time. I am able to run this portion just fine outside of the job.
------------------------------
/**RESTORE LOGS**/
IF @istartlogshipping = 0 and @ihr = 4 and @imn between 30 and 35

BEGIN

DECLARE @claimsbackuplog table ( [ID] [int] ,
        [vDBName] [varchar] (50) ,
        [vType] [varchar] (10) ,
        [vStatus] [varchar] (10) ,
        [vfilename] [varchar] (100) ,
        [bDeleted] [bit] ,
        [dBackupDt] [datetime],
        [AppliedLog] [bit])

insert into @claimsbackuplog
select * from openquery (claimssqlserver, 'select * from
dba_ops.dbo.claimsbackuplog nolock where bdeleted = 0')

DECLARE @logdbname2 varchar (30)
select @logdbname2 = vdbname from @claimsbackuplog where vtype = 'tranl' and
vdbname = 'claims'

DECLARE @cnt int
SET @cnt = 0

/**Remove users before restore **/
EXEC ExpungeUsers @logdbname2

DECLARE @fullcnt int, @trancnt int

/**Identify last full backup**/
select @fullcnt = (select [id] from @claimsbackuplog where vtype = 'full'
and vdbname = @logdbname2 and appliedlog = 1 and vstatus = 'success')
        
/**Identify tran backup after last full backup**/
If Object_Id('tempdb..#trancnttbl') is Not Null
Drop table #trancnttbl

create table #trancnttbl ([id] int identity (1,1),TranCnt int)
insert into #trancnttbl
select [id] from @claimsbackuplog where vtype = 'tranl' and vdbname =
@logdbname2 and appliedlog = 0 and vstatus = 'success' and [id] > @fullcnt

DECLARE @maxcnt int
select @maxcnt = ((select max([id]) from #trancnttbl) + 1)

DECLARE @mincnt int
SET @mincnt = 1
        
        /**Restore Transaction log backups**/
        while @mincnt < @maxcnt
         BEGIN

                DECLARE @Trancnt1 int
                select @Trancnt1 = (select trancnt from #trancnttbl where [id] = @mincnt)
                        
                DECLARE @LogShippingtranbackup varchar (100)
        
                select @LogShippingtranbackup = (select top 1 vfilename from
@claimsbackuplog where vdbname = @logdbname2 and vtype = 'tranl' and vstatus
= 'Success'
                                                 and [id] = @Trancnt1
                                                 order by dbackupdt desc)
                        
                DECLARE @EC2 int
                EXEC @EC2 = master.dbo.xp_restore_log
                @database = @logdbname2,
                @filename = @LogShippingtranbackup,
                @with='STANDBY="c:\temp\claimsstandby2.txt"',
                @logging = 1
                
                IF @EC2 = 0
                        BEGIN
                        update openquery (claimssqlserver, 'select appliedlog, [id] from
dba_ops.dbo.claimsbackuplog nolock')
                        SET appliedlog = 1
                        where [id] =@Trancnt1
                        END

                select @mincnt = @mincnt + 1
        
         END

END

Relevant Pages