SQL 2008 Transaction Log Backup Failure

the1paulcole
the1paulcole used Ask the Experts™
on
Hi,

I am in the process of setting up transaction log backups, every 45 minutes to a network share (a first for me). Until the last 2 backups, everything was running fine. Now, they seem to be sporadically failing with the following error:

Maintenance Plan: Trans Log Backup
Duration: 00:01:44
Status: Warning: One or more tasks failed.
Details:
Back Up Database (Transaction Log) (UPMSQL01) Backup Database on Local server connection
Databases: PMSQL03
Type: Transaction Log
Append existing
Task start: 2010-07-01T17:15:00.
Task end: 2010-07-01T17:16:45.
Failed:(-1073548784) Executing the query "BACKUP LOG [PMSQL03] TO  DISK = N'\\\\qmtad01\\pm_db_..." failed with the following error: "The operating system returned the error '64(failed to retrieve text for this error. Reason: 1815)' while attempting 'FlushFileBuffers' on '\\\\qmtad01\\pm_db_backup$\\transaction logs\\PMSQL03\\PMSQL03_backup_2010_07_01_171500_6874874.trn'.
BACKUP LOG is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 161496 pages for database 'PMSQL03', file 'PMSQL03_Log' on file 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:EXECUTE master.dbo.xp_create_subdir N''\\qmtad01\pm_db_backup$\transaction logs\PMSQL03''

GO
BACKUP LOG [PMSQL03] TO  DISK = N''\\qmtad01\pm_db_backup$\transaction logs\PMSQL03\PMSQL03_backup_2010_07_01_171500_6874874.trn'' WITH NOFORMAT, NOINIT,  NAME = N''PMSQL03_backup_2010_07_01_171500_6718618'', SKIP, REWIND, NOUNLOAD,  STATS = 10

GO

The backup file is appearing on the network share, the location can be accessed and pinged fine, yet the report comes back as failed and the transaction log is not being cleared which will definitely cause problems in the not so distant future.

Does anyone know what could be causing this sudden failure? I will be switching over to SIMPLE mode shortly as I don't feel comfortable leaving this over-night as the log file does fill up fairly fast.

Thanks

Paul
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008

Commented:
First, take a full backup followed by a log backup.  I have a feeling that the log chain has been broken.  Let me know if this works.
This souds to me to be a network issue; most likely caused by an intermittent connectivity glitch. The best practices recommend to backup locally and then transfere it over the network, but I understand that sometimes this may not be an option - e.g. if your short on the local storage space. Nevertheless I'd try the backup at least once on the local disk in order to eliminate the possibility of any issue with the DB itself.

Author

Commented:
ChapmanAndrew - I ran a manual backup straight after the failure without any issues so it's looking like it could be what ProjectChampion is suggesting. Where to begin looking I have no idea though.... I kept a continuous ping from the SQL server to the server where the backups are dumped and there wasn't any loss during the backup but it failed all the same. Unfortunately my network skills are a bit weak so it's going to be difficult to work this one out, unless someone could point me in the right direction...
Top Expert 2012

Commented:
Did you not see ProjectChampion's recommendation:  "The best practices recommend to backup locally and then transfere it over the network".  If that is not an option consider purchasing a reliable backup such as SQL Backup:
http://www.red-gate.com/products/SQL_Backup/index.htm

Author

Commented:
Set this up to run to an attached external RAID drive.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial