Daily Differential Backup Job Failing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Daily Differential Backup Job Failing

Daily Differential Backup Job Failing

Rate Topic

Display Mode

Topic Options

Author

Message

Abu Dina

Posted Monday, November 05, 2012 5:13 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 513, Visits: 2,248

Good afternoon,

I have a SQL Job which performs nightly differential backups of all databases on one of my production servers. Unfortunately the Job failed last night but I'm struggling to work out the cause of the problem.

The SQL Job error is:

SQL error log shows:

Please note that the database name in the SQL error log is not the same name in the SQL Job error. Although I've masked the database name, I can tell you that there are different.

I also checked space on the NAS and there's plenty of it.

Any ideas?

-----------------------------------
http://www.SQL4n00bs.com

Post #1381005

sanket kokane

Posted Monday, November 05, 2012 6:04 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:12 PM
Points: 323, Visits: 973

I cant see Error text Blink

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1381038

anthony.green

Posted Monday, November 05, 2012 6:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

The output of the text in the log file viewer for a job is only the top X amount of characters, the error will be somewhere else down the output, so either query the sysjobhistory table to get the full output or log the output to a file which you can review should the error happen again.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1381058

Jeff Moden

Posted Monday, November 05, 2012 7:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 33,113, Visits: 27,041

Must be the moon. I'm going through the same thing with a full backup that failed twice over the weekend. Once well before the time change and once after. The backup actually completed and then it said that the connection could no longer be resolved. Like you, I have plety o' space on the backup drive.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1381064

Abu Dina

Posted Monday, November 05, 2012 7:16 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 513, Visits: 2,248

Arrgghhh, the above output is actually the last part of the string from the job log history table so I'm afraid it's all I've got to work with.

-----------------------------------
http://www.SQL4n00bs.com

Post #1381069

anthony.green

Posted Monday, November 05, 2012 7:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

You will need to set the job to log to an output file then in the properties of the job which will capture the full output so that it can be reviewed on another failure.

Post #1381076

Abu Dina

Posted Monday, November 05, 2012 7:38 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 513, Visits: 2,248

Thanks Anthony.

I've reconfigured the job to output the log to a text file.

Let's see what happens tonight.

-----------------------------------
http://www.SQL4n00bs.com

Post #1381083

Abu Dina

Posted Tuesday, November 06, 2012 9:35 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 513, Visits: 2,248

It turns out that two of my databases didn't have full backups which explains why the differential backup job failed.

Thanks for the suggestion Tony!

-----------------------------------
http://www.SQL4n00bs.com

Post #1381638

« Prev Topic | Next Topic »

Permissions