SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup "fails"


Backup "fails"

Author
Message
JimS-Indy
JimS-Indy
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 444
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
rollercoaster43
rollercoaster43
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 435
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.. Smile 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 Wink

Cheers!!!!
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1793
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.
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7183 Visits: 2250
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."
JimS-Indy
JimS-Indy
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 444
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
JimS-Indy
JimS-Indy
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 444
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search