Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Hello all,
I'll get to the problem, which I have a tough time finding out the
root cause.
1) I have SQL Server 2000 Installed, with the following patch/SP
version:
Version: 8.00.760
2) My database is running on a server with Windows Server 2003 SP2.
The Problem:
All of the automated scheduled backups used to work all perfectly
well. We have an IC backup that we've implemented and it hasn't ever
caused us issues (since it is our standard)...BUT...when our
security team decided to update the Service Pack to SP2, we noticed
that the scheduled backup job would cease to run...YET...it shows
that the backup has been taken on the Jobs Activity Monitor (Even in
the History of that job), but once we look at it, the job only fired
and completed in 1 second, when it takes on average (1-2 mins).
But here's something even more wierd...it takes a backup whenever it
feels like...notably every 2-3 days...and everything is perfect. Then
you get 3-4 days of no backups. We've been watching this odd behaivour
for the past 1 month or so.
Is there a patch I am missing for SQL Server 2000? Or is the Windows
Server 2003 SP2, causing me to not properly take my backups since it
is blocking or has stopped a particular step.It sounds strange. Have you looked into ERROR.LOG ? Any entries?
"Mohd Al Junaibi" <mohamed.aljunaibi@.gmail.com> wrote in message
news:d9f861f4-3667-49cc-8eae-a51b75aff9db@.s19g2000prg.googlegroups.com...
> Hello all,
> I'll get to the problem, which I have a tough time finding out the
> root cause.
> 1) I have SQL Server 2000 Installed, with the following patch/SP
> version:
> Version: 8.00.760
> 2) My database is running on a server with Windows Server 2003 SP2.
> The Problem:
> All of the automated scheduled backups used to work all perfectly
> well. We have an IC backup that we've implemented and it hasn't ever
> caused us issues (since it is our standard)...BUT...when our
> security team decided to update the Service Pack to SP2, we noticed
> that the scheduled backup job would cease to run...YET...it shows
> that the backup has been taken on the Jobs Activity Monitor (Even in
> the History of that job), but once we look at it, the job only fired
> and completed in 1 second, when it takes on average (1-2 mins).
> But here's something even more wierd...it takes a backup whenever it
> feels like...notably every 2-3 days...and everything is perfect. Then
> you get 3-4 days of no backups. We've been watching this odd behaivour
> for the past 1 month or so.
> Is there a patch I am missing for SQL Server 2000? Or is the Windows
> Server 2003 SP2, causing me to not properly take my backups since it
> is blocking or has stopped a particular step.|||I do get entries in the ERROR.LOG file.
But they are only there once the backup is successfully done. The
backups are to happen on a daily basis...but you'll notice in the
ERROR.LOG that there are gaps of 2,3 and sometimes 4 days in between.
Again, this is strange, as in the Jobs Activity Monitor, it says that
the job was successfully run, yet in reality it didn't as it fired and
completed the job within 1 second...which is big sign that OS is
stoping something. But I'm speculating.
Here's the error logs (the database I'm talking about is:
"MyDatabase") I've edited just to make it as informative as possible:
2007-10-31 11:08:13.03 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-10-31 11:08:13.03 server Copyright (C) 1988-2002 Microsoft
Corporation.
2007-10-31 11:08:13.03 server All rights reserved.
2007-10-31 11:08:13.03 server Server Process ID is 1892.
2007-10-31 11:08:13.03 server Logging SQL Server messages in file
'd:\MSSQL\log\ERRORLOG'.
2007-10-31 11:08:13.06 server SQL Server is starting at priority
class 'normal'(4 CPUs
detected).
2007-10-31 11:08:13.71 server SQL Server configured for thread mode
processing.
2007-10-31 11:08:13.78 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000]
Lock Owner Blocks.
2007-10-31 11:08:14.09 server Attempting to initialize Distributed
Transaction
Coordinator.
2007-10-31 11:08:17.34 spid3 Starting up database 'master'.
2007-10-31 11:08:22.40 server Using 'SSNETLIB.DLL' version
'8.0.760'.
2007-10-31 11:08:22.40 spid5 Starting up database 'model'.
2007-10-31 11:08:22.43 spid3 Server name is 'XYZ'.
2007-10-31 11:08:22.62 spid8 Starting up database 'msdb'.
2007-10-31 11:08:22.64 spid12 Starting up database 'MyDatabase'.
2007-10-31 11:08:23.25 spid5 Clearing tempdb database.
2007-10-31 11:08:26.45 server SQL server listening on 10.40.50.210:
1433.
2007-10-31 11:08:26.45 server SQL server listening on 127.0.0.1:
1433.
2007-10-31 11:08:26.51 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2007-10-31 11:08:26.51 server SQL Server is ready for client
connections
2007-10-31 11:08:27.70 spid5 Starting up database 'tempdb'.
2007-10-31 11:08:28.17 spid5 Analysis of database 'tempdb' (2) is
100% complete
(approximately 0 more seconds)
2007-10-31 11:08:28.64 spid3 Recovery complete.
2007-10-31 11:08:28.64 spid3 SQL global counter collection task is
created.
2007-10-31 11:08:32.39 spid51 Using 'xpsqlbot.dll' version
'2000.80.194' to execute
extended stored procedure 'xp_qv'.
2007-11-01 00:10:01.07 spid53 Using 'xplog70.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_cmdshell'.
2007-11-01 00:10:03.07 spid53 Using 'xpstar.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_instance_regread'.
2007-11-01 08:06:08.26 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31369, first LSN:
38008:338:1, last LSN:
38008:404:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
errors and repaired 0 errors.
2007-11-08 00:10:28.80 spid55 Starting up database 'MyDatabase'.
2007-11-08 00:10:47.85 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31513, first LSN:
38022:1404:1, last LSN:
38022:1408:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-10 00:10:00.80 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:00.91 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:02.22 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1798:1, last
LSN: 155:1800:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-10 00:10:02.49 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-10 00:10:02.57 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:04.30 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 980:417:1, last
LSN: 980:420:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-10 00:10:27.83 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.86 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.93 spid55 Starting up database 'MyDatabase'.
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-19 00:10:26.45 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:29.02 spid57 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.05 spid57 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.12 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:48.69 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31881, first LSN:
38051:74:1, last LSN:
38051:78:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-21 00:10:01.23 spid56 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:01.35 spid56 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:02.71 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1920:1, last
LSN: 155:1922:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-21 00:10:02.98 spid56 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-21 00:10:03.05 spid56 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:04.96 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 981:198:1, last
LSN: 981:201:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-21 00:10:26.87 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:29.51 spid56 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.55 spid56 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.63 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:49.37 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32033, first LSN:
38073:707:1, last LSN:
38073:711:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-24 00:10:00.47 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:00.60 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:02.00 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1969:1, last
LSN: 155:1971:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-24 00:10:02.28 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-24 00:10:02.36 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:04.17 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1843, first LSN: 981:274:1, last
LSN: 981:277:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-24 00:10:25.86 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:28.50 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.53 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.61 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:48.19 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32161, first LSN:
38101:76:1, last LSN:
38101:80:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).|||I've just realised that the main issue is that in one of backup
scripts that runs (and it is automated)...we have used:
isql command to run the back, and this would at times work, and also
not work.
by using the osql command instead, everything worked out fine.
Example:
Instead of having your statement:
isql -Usa -P -SSERVER
You have:
osql -Usa -P -SSERVER
That worked for me. The statement for the backup was in "isql" and I
converted it to "osql" and the rest remained untouched, and everything
started working again.