SQL Server Performance Forum – Threads Archive
Log Shipping Transaction Backup Error
Hi All, I have set up logshipping between two servers. The transactional log backups are set to run evey 10 minutes. The problem is that, the Transaction Log Backup job on the primary server keeps failing at certain times, for instance every 30 minutes (it runs fine at the 10th, 20th, 40th minutes etc, but just fails every 30th minute eg it fails at 7:30, 8:00, 8:30 etc) it is totally random. If I re-configure logshipping, it fails at another interval. I do not have another transactional log backup set-up on that server nor do I have DTSs that copy objects from this server to another (I learnt that these could interrupt transaction backups). The server is set to Full recovery mode. I’m not sure what is still causing the backup to fail at certain times. Following, please find the error on the event log:Source: SQLSERVERAGENT
Category: Job Engine
Event ID: 208
Type: Warning SQL Server Scheduled Job ‘Transaction Log Backup Job for DB Maintenance Plan ‘LogShipping_Timesheeting” (0xD66AD2C1F7537C409C34CC6DB263D5CF) – Status: Failed – Invoked on: 2007-02-28 08:00:03 – Message: The job failed. The Job was invoked by Schedule 80 (Schedule 1). The last step to run was step 1 (Step 1). I have reconfigured log shipping several times, but it still keeps failing at random intervals and hence doesnt seem stable to me. Could transactional locking cause such problems? Am I missing anything? What can I do to prevent it from happening? Any advice would be appreciated. Thanks much. Cheers,
Anna.
Do you have any NON LOGGED operations set on the database at those times?
Take help of profiler to see what kind of statements are running at those tiems. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi guys… we are having the EXACT SAME PROBLEM with our SQL Transaction Log backups. Was there ever a satisfactory resolution to this? I keep having to manually delete the old backup files in order to free up disk space on our SQL server. What do I need to look at to find out what’s causing the problem? Keep in mind that I’m not that proficient at SQL, so I’ll need a simplified answer if possible. Thanks!!
[8]
Dan Bressler
Hi Arajendran,<br /><br />We have a main database (30 Gb) with a Maintenance Plan set as follow :<br /> Full backup every night at 00h05<br /> Transaction Log backup every hour<br /><br />When setting up LogShipping, verify the BackupLog interval so that the LogShipping (backup log) does not run during the same time as the hourly Transaction Log Backup since <Backup Log> locks the database (.ldf) I think it’s an exclusive task.<br /><br />If it’s not the case, it maybe because another process is locking the Transaction Log database during the schelude when a Backup Log is suppose to run.<br /><br />LogShipping use the same command <Backup Log> as the hourly Transaction Log Backup.<br /><br />Hope this will help [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />BM
quote:Originally posted by dbressler
Hi guys… we are having the EXACT SAME PROBLEM with our SQL Transaction Log backups. Was there ever a satisfactory resolution to this? I keep having to manually delete the old backup files in order to free up disk space on our SQL server. What do I need to look at to find out what’s causing the problem? Keep in mind that I’m not that proficient at SQL, so I’ll need a simplified answer if possible.
Thanks!!
[8]
Dan Bressler
[8]
Dan Bressler
Hi Dan, If you’re using SQL 2005, you can add a Maintenance Cleanup Task to your backup plan so it will delete old backups base on the date/time of the file.
it’s in the Toolbox of the Maintenance Plan Tasks. Hop this help ! BM
The fix that worked for us is as follows: Solution:
Databases have an option know as "recovery mode", and there are 3 choices for this option.
If this is set to "simple", a transaction log cannot be created for the database and so the entire backup job fails. To find what your databases are set to:
– Open up Enterprise manager
– Right click on your databases and click properties
– Select the Options tab If there are any databases that you must backup their transaction log, this option must not be set to simple. So I went into each database and changed two of them to FULL instead of SIMPLE. These databases were: cAsset
cSupport This worked! No more errors in the EventViewer… and no more disk space being taken up by undeleted logs files.
Dan Bressler
Actually, we have another issue on one of our NT SERVERS. It is running SQL 7.0 and runs an hourly Transaction Log Backup. The backup log files are taking up WAY too much room on the hard drive and we need to find out how to shrink the size of these files, or shrink the database. Is there any way to do this? Or maybe a way to run a daily maintenance job that automatically deletes older transaction log files? Any help would be appreciated, just remember it’s SQL 7.0 Dan Bressler
Maintenance plan has that capability to delete the older files, you can also have another scheduled job to do so. If the backup files are big in size then you can either run the transaction log backup frequently or limit the queries in smaller transactions to keepup the sizes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>