Having problems with SQL Agent in SQL 7. Just noticed that my backup hasn't
been running since the 17th. Went into SQL Agent, and the 4 jobs that it
does (integrity checks, optimizations, transaction log backup, and db
backup) were all hanging -- still running "step 1" from the 18th.
I stopped SQL Agent and restarted it, and tried running the integrity
checks. It hung.
Stopped SQL Agent and restarted it and tried running optimizations. It
hung.
Stopped SQL Agent and restarted it and tried running db backups. The
backups
job runs three backups: master, msdb, and my database. Interestingly, the
backup job created a backup for master and msdb, but didn't create a
backup
for my database. The job just continually showed that it was being
processed
when I looked in EM, under Management, SQL Server Agent, Jobs.
I stopped SQL Agent again, and restarted it. Then I did a manual backup of
my database. The manual backup worked fine. Also, the database itself
seems
to have been working fine these past 11 days. But, for some reason, SQL
Agent won't run the jobs for my database.
As noted, the last successful running of the jobs was on the 17th, between
4
and 5 a.m. On that same day, between 3 and 4 a.m., the sys admin applied
some Windows 2000 Server security updates and rebooted the server. Then
the
jobs ran an hour later. Then, on the 18th, the integrity checks ran
successfully at 4:10. After that, nothing ran successfully.
Even stranger, still, are the SQL Server Logs.
The SQL Server logs show SQL Server stopping at 3:29 am on the 17th
(probably at the time of reboot), and then restarting at 3:35 am, and then
completing the backups of the three databases at 4:37 and 4:38 am. That's
all fine.
Then, that night, at 10:08 pm on the 17th, there's a log entry that reads:
"Using 'xpsql70.dll' version '2000.28.09' to execute extended stored
procedure 'xp_msver'"
Then, at 11:00 pm that same night, EVERY database was backed up (master,
model, msdb, Northwind, pubs, my database, and another I have there called
test1). They all got backed up at 11:00 pm on the 17th, even though I have
no maintenance plan that specifies that they should be backed up.
Then, after the 17th, according to the logs, those 7 databases got backed
up
every night between 10 and 11 pm -- but at a different time each night:
10:41 on the 18th; then 10:37 the following night; then 10:41; then 10:42;
then 10:51; 10:57; etc. Clearly, these backups are happening at the end of
another job, which is taking longer and longer.
But the question still remains: what caused these 7 backups to happen in
the
first place? The Windows 2000 Server updates that the admin applied the
morning of the 17th? And why do my regular Agent jobs not execute, but
just
hang indefinitely?
Also, though the log shows these 7 backups running every night, I don't
see
the backups anywhere. Looking at the properties for one of the log
entries,
I see the following:
Database backed up with following information: Database: Northwind,
creation
date and time: 2002/05/19(12:03:16), pages dumped: 371, first LSN:
26:426:1,
last LSN: 26:428:1, sort order: 52, striped: 0, number of dump devices: 1,
device information: (FILE=1, TYPE=VIRTUAL_DEVICE:
{'Northwind_00__8b6a35a5_2ead_4045_8bc2_c975392f0020_'}).
So it's backing up to a virtual device, which explains why I don't see the
backups anywhere.
Anyway, any ideas as to what might be going on and how I may be able to
fix
it so that my regular maintenance jobs and backups are able to run again
would be appreciated. Thanks!
Neil
|
