Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup "fails" Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 4:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1565730
Posted Monday, April 28, 2014 11:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:14 AM
Points: 60, Visits: 401
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!!!!
Post #1565758
Posted Wednesday, April 30, 2014 2:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 203, Visits: 816
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.
Post #1566267
Posted Wednesday, April 30, 2014 3:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1566284
Posted Wednesday, April 30, 2014 2:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1566605
Posted Saturday, May 3, 2014 10:02 AM This worked for the OP Answer marked as solution


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1567277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse