April 28, 2014 at 4:56 pm
A few months ago, I added a simple, one-line job to backup one of my SQL Server 2012 databases -- a compressed noon backup. It has been working fine for months. Last week, I started to get "Job failed" emails saying the job was failing. But when I checked the log, it shows as succeeding. I should note that the backup file seems to be created just fine.... Any ideas?
Here's one of the "failed" logs (CRLFs mine...):
Message
Executed as user: WINNTDOM\sqlserveragent.
BACKUP database [CPAS] to disk = '\\winntdom\root\common\xyz\secure\Construction\Access\All
Database Backup\SQLServerNoonBackup\CPAS_db_201404281216.BAK' WITH INIT, COPY_ONLY, COMPRESSION [SQLSTATE 01000] (Message 0)
Processed 133984 pages for database 'CPAS', file 'CPAS' on file 1. [SQLSTATE 01000] (Message 4035)
Processed 2 pages for database 'CPAS', file 'CPAS_log' on file 1. [SQLSTATE 01000] (Message 4035)
BACKUP DATABASE successfully processed 133986 pages in 17.642 seconds (59.333 MB/sec). [SQLSTATE 01000] (Message 3014). The step succeeded.
Jim
April 28, 2014 at 11:49 pm
I know this is weird but I have faced situations where the Advanced tab of the jOb step had the option selected as :
On Success -> Quit the Job reporting Failure and then there were False alerts.. 🙂 This was done by some Junior DBA to check if the job really fires an alert on failure (Although there were other better ways that could be used)
I Hope this is not the case here as well 😉
Cheers!!!!
April 30, 2014 at 2:41 am
I assume you have checked that the message sent on the Success condition hasn't just been copied unaltered from the one sent on the Fail condition.
April 30, 2014 at 3:42 am
Yes, message you have posted is clearly mention that backup is successful. Resend the correct message so people can help.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 30, 2014 at 2:17 pm
That's the point. I suddenly started getting emails (this has been running fine for months with no such emails...) saying:
JOB RUN: 'Noon Compressed Backup to H: drive' was run on 4/28/2014 at 12:16:00 PM
DURATION: 0 hours, 0 minutes, 5 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 19 (Daily Backup to H Drive). The last step to run was step 1 (Backup to H: drive).
Jim
May 3, 2014 at 10:02 am
Mystery Solved!
I am a part-time consultant for this client, so lots of things happen when I'm not aware. It turns out that the client decided to "virtualize" my server. No problem, it's likely to make it safer. But they didn't talk to me about it until they had spun up the VM and copied over my entire server, then fired it up! As a result, the new server began running SQL Agent jobs alongside the old server.
When the evening backups ran, the filename included the time down to the second. By contrast, the noon backup only had a filename that included the time down to the minute. So, when one of the servers created a backup file seconds before the other one, the late-comer would find a duplicate file name, and an open one at that! Thus, it would fail. Every day....
The client called me yesterday and asked that I test the new server so they could continue the process, and it was the first time I'd heard of it. I checked the email and found that it had come from the new server, not the old one. Eureka!
Thanks for listening. Sorry for the confusion.
Jim
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply