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?



Show quoteHide quote

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'}).

Performance monitoring specific database
-- error al crear plan de mantenimiento --
High Pagelatch and threadpool
Backup error
Restoring a column in a table