SQL Server Performance Forum – Threads Archive

Transaction log backup error

Hi There<br /><br />I have a weird problem.<br />Today we rebooted the backup server, this server has shared folders for 4 databases to backup their transaction logs to.<br /><br />After rebooting the backup server the transaction log backups started failing.<br /><br />NOTE this sql is inside a sql job that is scheduled to run every 15 minutes.<br /><br />The first part simply assigns a date and time to the log backup name:<br />————————————————————————————<br />declare @BackupName varchar(100)<br />select @BackupName = N’\JDGBACKUP01ODS3PRO_LogODS3PRO_Log'<br />+ CONVERT(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, GETDATE(), 112) + REPLACE(CONVERT(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, GETDATE(), 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ‘:’, ”) + ‘.TRN'<br /><br />BACKUP LOG [ODS3PRO] TO <br />DISK = @BackupName WITH RETAINDAYS = 14, NOINIT , NOUNLOAD , <br />NAME = N’ODS3PRO Transaction Log Backup’, NOSKIP , STATS = 10, NOFORMAT<br />————————————————————————————<br /><br />When i run this in query analyzer I get the following output/error:<br />————————————————————————————<br />21 percent backed up.<br />42 percent backed up.<br />63 percent backed up.<br />85 percent backed up.<br />100 percent backed up.<br />Processed 563 pages for database ‘ODS3PRO’, file ‘ODS3PRO_Log’ on file 1.<br />Server: Msg 3013, Level 16, State 1, Line 5<br />BACKUP LOG is terminating abnormally.<br />————————————————————————————<br /><br />I have investigated the error 3013, this happens when the sql server agent account which runs the backup job does not have permissions to backup on a remote server.<br /><br />This is where it gets weird, when i look at the shared folder the backup logs are there, as you can see from the output it completes up to 100%, then fails??? <br /><br />It appears that the logs did backup, but they keep getting bigger , indicating that the transaction log is not truncating after the backup, because it thinks that it failed.<br /><br />The domain account for sql server agent has full permissions for the shared forlders?<br /><br />PLEASE HELP ???<br /><br />Thanx

---

Intersting. The log backup file is created the moment the backup starts, which is why you see the files there. The fact that the backup fails at 100% though probably means that the backup files might not be complete.<br /><br />Remove the STATS = 10 option from the backup command and try and run the backup statement. The reason I suggest this is because it is possible that the error is related to shared memory not having being initialised when SQL Server started. The stats output information relies on shared memory so if you remove this option and it works then you know it’s a shared memory problem – in which case you will most likely have to reboot the server again.<br /><br />The other thing to check is the sql server error log. Somewhere near the top you should see:<br /><br />SQL server listening on TCP, <b>Shared Memory</b>, Named Pipes.<br /><br />Anyway, remove the stats option and see if that helps. If nothing else it will eliminate that as a potential cause of the problem.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by [email protected]</i><br /><br />Hi There<br /><br />I have a weird problem.<br />Today we rebooted the backup server, this server has shared folders for 4 databases to backup their transaction logs to.<br /><br />After rebooting the backup server the transaction log backups started failing.<br /><br />NOTE this sql is inside a sql job that is scheduled to run every 15 minutes.<br /><br />The first part simply assigns a date and time to the log backup name:<br />————————————————————————————<br />declare @BackupName varchar(100)<br />select @BackupName = N’\JDGBACKUP01ODS3PRO_LogODS3PRO_Log'<br />+ CONVERT(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, GETDATE(), 112) + REPLACE(CONVERT(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, GETDATE(), 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ‘:’, ”) + ‘.TRN'<br /><br />BACKUP LOG [ODS3PRO] TO <br />DISK = @BackupName WITH RETAINDAYS = 14, NOINIT , NOUNLOAD , <br />NAME = N’ODS3PRO Transaction Log Backup’, NOSKIP , STATS = 10, NOFORMAT<br />————————————————————————————<br /><br />When i run this in query analyzer I get the following output/error:<br />————————————————————————————<br />21 percent backed up.<br />42 percent backed up.<br />63 percent backed up.<br />85 percent backed up.<br />100 percent backed up.<br />Processed 563 pages for database ‘ODS3PRO’, file ‘ODS3PRO_Log’ on file 1.<br />Server: Msg 3013, Level 16, State 1, Line 5<br />BACKUP LOG is terminating abnormally.<br />————————————————————————————<br /><br />I have investigated the error 3013, this happens when the sql server agent account which runs the backup job does not have permissions to backup on a remote server.<br /><br />This is where it gets weird, when i look at the shared folder the backup logs are there, as you can see from the output it completes up to 100%, then fails??? <br /><br />It appears that the logs did backup, but they keep getting bigger , indicating that the transaction log is not truncating after the backup, because it thinks that it failed.<br /><br />The domain account for sql server agent has full permissions for the shared forlders?<br /><br />PLEASE HELP ???<br /><br />Thanx<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com

---

Hi SQLDBcontrol Ok i have check all of this it is not the problem.
It is very weird, the log file backups and it has a big size, my all means it looks like the backup was sucessfull, but sql server thinks that it was not, therefore not truncating the log, and they just keep growing, the service domain account and permissions are all 100% ??? Thanx

---

There’s another possibility. When starting up, SQL Server, failed to bring up the original log file online so it created another one. Meaning that you will need to perform a full database backup before running any log backups. Try doing a full backup and then see if the log backups work. I’m sure there’s a bug associated with this problem. I’ll see if I can find the article.
Karl Grambow www.sqldbcontrol.com

---

Hi I removed the stats option and still the same error.
This is in the sql log :
—————————————————————————————-
BACKUP LOG [ODS3PRO] TO
DISK = @BackupName WITH
2005-11-29 11:22:05.67 spid243 BackupDiskFile::RequestDurableMedia: failure on backup device ‘\JDGBACKUP01OLTP2PRO_LogOLTP2PRO_Log20051129112203.TRN’. Operating system error 87(error not found).
—————————————————————————————- I cannot find much on this error but i am assuming it is the same as the sql error, that is that it cannot access the share, although it has full permissions ???

---

I assume you did a full database backup just now. Try backing up the log to a local drive (just need to see if this works or not) and create another share and try to back up to the other share (this will tell you if it’s a specific problem with the original share). I found an article which seems to point to similar problems (i.e. the file is created but the error makes it seem as if the backup failed). the error messages are different but it’s worth looking at. http://support.microsoft.com/default.aspx?scid=kb;en-us;827452 On another note, is there any reason why you can’t backup the log files locally and then copy them to the share? Backing up to a share can be a bit of a problem sometimes (as you’ve found out) and it’s generally prefferable to backup locally and then copy the backups to the share. This places less load on SQL Server and it means you get two backups (one of which is local).

Karl Grambow www.sqldbcontrol.com

---

HI Thanx for all the help SQLDBcontrol.
Turns out the remote backup server is broken, keeps rebooting, serious system error s that require hotfixes so i am sure this is related. I found that exact same link, so we are going to get the hotfixes for the server as i am sure it is the problme. Thanx again

---

Cool, good luck with fixing the backup server.

Karl Grambow www.sqldbcontrol.com

---

]]>