Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First of all, I'm very new at stackexchange, so please bear with me.

I'm running a SQL Server 9.0.4060.

My problem is: my SQL Server backup keeps failing on a lot of the databases.

The Maintenance plan runs to different backup jobs:

DIFF backup everyday at 20:30 and FULL backup every Sunday at 20:30.

I get these two errors foreach database that fails to complete the backup:

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
BACKUP failed to complete the command BACKUP DATABASE (dbname) WITH DIFFERENTIAL. Check the backup application log for detailed messages.

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
Error: 3041, Severity: 16, State: 1.

Extra errors.

Date     06-11-2012 17:53:27 
Log      SQL Server (Current - 07-11-2012 11:43:00) 
Source   Logon 
Message  Error: 18456, Severity: 14, State: 16.

Date     06-11-2012 17:53:27 
Log      SQL Server (Current - 07-11-2012 11:43:00) 
Source   Logon 
Message  Login failed for user 'sa'. [CLIENT: <local machine>]

Date     06-11-2012 20:30:11
Log      SQL Server (Current - 09-11-2012 11:27:00)
Source   spid15s
Message
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

There are to other similar errors to the one above, with 'SQL Plans' and 'Object Plans'. Instead of 'Bound Trees'.

If you guys need any other information, just say the word.

share|improve this question
    
Are there errors preceding the 3041 in the sql server error log? (not the windows log) –  Alex K. Nov 7 '12 at 12:30
    
Yes, there are these two errors: " Date 06-11-2012 17:53:27 Log SQL Server (Current - 07-11-2012 11:43:00) Source Logon Message Error: 18456, Severity: 14, State: 16. " " Date 06-11-2012 17:53:27 Log SQL Server (Current - 07-11-2012 11:43:00) Source Logon Message Login failed for user 'sa'. [CLIENT: <local machine>] " –  Arviddk Nov 7 '12 at 12:48
    
are there more errors than that? –  swasheck Nov 7 '12 at 21:24
    
Yes @swasheck, i added a few more errors in the description –  Arviddk Nov 9 '12 at 13:52
    
are these native backups or are you using other backup software? –  swasheck Nov 9 '12 at 16:05
show 1 more comment

migrated from stackoverflow.com Nov 7 '12 at 13:52

This question came from our site for professional and enthusiast programmers.

2 Answers

As Max pointed out, the sa login error doesn't corrolate to the backup time as it happens several hours before, however the 2nd one does. Have you looked into: http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

http://www.sqlservercentral.com/Forums/Topic377116-146-1.aspx#bm380502

Do any of the items in the MSDN blog play a factor in your environment? Most common one would be the AUTO_CLOSE ON. Let us know, thanks.

share|improve this answer
    
AUTO_CLOSE is enabled on 10 out of 164 DB's. I counted 70 occurrences of 'Bound Trees','SQL Plans' and 'Object Plans' at 13/11/2012 21:45:24. At 13/11/2012 21:53:15 it was reset to 1 occurrence of each. Hope this helps. –  Arviddk Nov 14 '12 at 12:59
    
Does this help you identify the error @SQL-Learner ?. I asked the other IT gyus in my department if they know if any of the other commands mentioned in the MSDN blog. –  Arviddk Nov 16 '12 at 12:30
    
Hi Arviddk, let me look further into it today! Thanks. –  Ali Razeghi Nov 16 '12 at 17:22
    
Okay, looking forward to your response! –  Arviddk Nov 19 '12 at 15:04
add comment

Please, Take a look here: How to troubleshoot "Msg 3041" in SQL Server

Excerpt from page:

Message 3041 is a generic report about the backup failure. To understand the cause of the error and resolve it, you must use the SQL Server Error log entries that occur before the 3041 event entry. Typically, 3041 is preceded by a detailed error message that states the real reason for the backup failure. The detailed error messages are written only to the SQL Server error log and to the client application. The detailed message is not reported in the Application log in Windows.

Update (After reading @dezso comment)
I've seen the "extra error" reported, but I'm not sure that they are related to the backup error. The backup error is dated at 20:31:06, but the "extra error" are dated at 17:53:27, so the extra error happened about 2 hours and 40 minutes before the actual backup error. And these extra error are related to a logon issue.

I don't think that a logon issue happening at 17:53:27 could result in a backup failing at 20:31:06. So a think that the backup error, and that the "extra error" are unrelated.

I also don't think that a generic error happening at 17:53:27 could result in a backup failing at 20:31:06 (unless the DB is really big). @Arviddk report that "my SQL Server backup keeps failing on a lot of the databases" I think it's safe to assume that "a lot" mean more than 10 DB.
So I think that if a single backup of a single DB take more than 2 hours and 40 minutes, you cant have 10 or 20 DB like that on a single server... or your server will spend it's life doing only backup. So I assumed that the single backup take a lot less than 2 hours and 40 minutes, so I think that the backup error and the extra error are not related.

And the last "cachestore flush / 'Bound Trees'" error reported is dated at 06-11-2012 20:30:11, so it's something happened after the backup error. Anyway a problem related to the cache shouldn't result in an error (event if it can slow down something).

Some questions
Some questions that could help identify the issue:

  • How much time does it take a FULL backup to execute?
  • How much time does it take a DIFF backup to execute?
  • Does the error happen only on DIFF backup?
  • Which percentage of backup does fail? (1%? 10% 50%)
  • Maybe you can try to only do FULL backup to check if it's an issue related to the DIFF backup? (I've never done any DIFF backup on SQL Server, I don't have experience with them)

According to the MS website in the SQL Server log there should be some others error related to the failed backup, but I'm expecting something more near, in time, to the time at which the backup is failing.

share|improve this answer
    
OP has already included error messages from before 3041 - do you think it's still not enough? If yes, please modify your post accordingly. –  dezso Nov 10 '12 at 9:41
    
The backup fails for both DIFF and FULL. It takes in average about 2-3 minutes for the backup to finish. (The last error is logged 2-3 minutes after the backup starts). I counted a 100 of these errors on the DIFF backup from 13/11/2012 - "BACKUP failed to complete the command BACKUP DATABASE (dbname) WITH DIFFERENTIAL....." –  Arviddk Nov 14 '12 at 12:54
    
@Arviddk you talk about 164 DB's, but are those DB's all on a single server? How are scheduled the DB backups? Maybe that the error you see can be related to some kind of scheduled backups overlapping? (I don't know if running concurrently 2 or more T-Sql backup can create errors... I've never tried) If the backups are overlapping in time, maybe it's possible to force them to run 1 by 1 in a serial way? (On my server I have a single script that backup all my DB's, so there are no overlapping in backup execution) –  Max Nov 14 '12 at 14:59
    
Yes there are 164 DB's on the server. But 99 of them are OFFLINE. The 'Auto_close' function is enabled on some of the DB's, so that they close whenever the ain't in use. (as far as i understand?) –  Arviddk Nov 14 '12 at 15:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.