Tell me more ×
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.
up vote 2 down vote favorite

I was asked to change the DFS share for our log shipping on all our SQL servers (SQL 2008 on windows 2008) to point to the new share due to some IO issues on the previous one. But now, ocassionally, on one of the important production server, I get the following error every so often. Please note that I do not get this for every transacton file and also that the error seems to be occuring only from one of the server. Once the log shipping fails with this error, our DR logshipping is broken (ofcourse) But all the consecutive log shipping backups for this server just continue working fine.

Message 2012-06-18 09:40:35.37 * Error: Backup failed for Server 'XXXX'. (Microsoft.SqlServer.SmoExtended) 2012-06-18 09:40:35.37 Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) 2012-06-18 09:40:35.37 Error: Write on "\\...FILE23.trn" failed: 2(The system cannot find the file specified.) BACKUP LOG is terminating abnormally. 100 percent processed.(.Net SqlClient Data Provider) * 2012-06-18 09:40:35.39 ----- END OF TRANSACTION LOG BACKUP -----

My questions:

  1. Why would the backup for a transaction fail every few hours only? The consecutive backups just run fine.
  2. What are the factors I need to check for this type of issue?
  3. Why would I get an error as "cannot find the file" when it is supposed to be doing a write?

Any help on this is deeply appreciated.

Thanks in advance

share|improve this question

1 Answer

up vote 1 down vote

Since your network drive is available most of the times and the backup job only fails randomly, I would first check the network. It could very well be the network issue. I would also look at the intervals the backup job fails, to see if there is a pattern to it.

Coming to restarting the logshipping part - the easier method is to restore the full backup and the standby server and reconfigure. But it depends on the size of your database, resources available etc

share|improve this answer
Thanks. If it is a n/w issue, it happened across all other sql servers as well. But this seems to be happening only from one server and maybe once in 5 hours since yesterday. I am wondering if this is some kind of bug in windows on that server and DFS share. I am sure there must be better ways of restoring that restoring the WHOLE DB. Like I said, in my case I did a DIFF BACKUP restore and I am back in business. But what if the last back to happen was only tran files and if there were no diff /full backups taken after that, how would I restore the up-to the minute data to DR in that case? – DBAuser Jun 18 '12 at 15:06
We changed back to the old share and so far no issue. The SA's are looking to see why only that server is behaving differently than the rest of the SQL servers which is kind of weird. – DBAuser Jun 19 '12 at 15:46

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.