question

askmlx121 avatar image
askmlx121 asked

log_reuse_wait_desc displays log_backup regualr interval every 2 hours even schedule tlogbackup every one hour why??

Hi Experts, I am using SQL 2005, We have 20 database, recovery model Full My problem is We are scheduling the Transaction log backup every one hour it runs fine in every hour Successfully. But am monitoring the database status using **select log_reuse_wait_desc,* from sys.databases query** the field log_reuse_wait_desc displays log_backup every 2 hours for **few database.** after displaying am run manually **backup log dbname to disk='location'** To solve this. Why did log_backup comes even I scheduling the Transaction log backup hourly basis? Any info/Suggestion much Appriciated Thanks....
sql-server-2005transaction-log
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
A few thoughts... How have you scheduled the Transaction Log backup - are you using a Maintenance Plan, or a script of some sort? If it's a script, then is it a home-brew job, or one from elsewhere? How is the script being called? Is it always the same database(s) that have this problem? If so, then it could be one of the following: * Dodgy database name - name with a space, hyphen or a dot, or something else, not being handled properly by your backup script * insufficiently dynamic script failing to take into account databases created after the script was implemented, or databases that were changed from SIMPLE to FULL recovery after the script was implemented * Script calling the backup routine putting a filter on the TLog backups.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Hmm. If it's different databases every time, then it sounds like none of my suggestions above are the right ones. So. Are there any errors relating to the TransactionLog backup jobs?
2 Likes 2 ·
askmlx121 avatar image askmlx121 commented ·
Hi Thomas rushton Thanking you for your reply. Implemented the Transaction log backup with maintenance plan no manual scripts are used every time diffrent database are shown in sys.databases database name have no space or no hypen or no dot used I will check and tell you for any switching the databases happened or not Recently
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
thankx...thomas Rushton No Transaction log backup jobs error came at all. But once when Database daily backups are not being taken any of 1 or 2 days then Transaction log backup jobs fails. after resuming the daily database backup jobs and transaction log backups runs fine
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What's causing the daily backups to fail? I don't understand why that would then cause the transaction logs to fail - the two should be relatively independent. However, if the daily backup job fails due to, say, lack of disk space, then that could then impact on the transaction logs... ...so what's happening to the tidy-up job (or whatever deletes the old backups?)
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
Daily Backup jobs are monthly once due to Some network error(Backup jobs maintained by third party tools in network) So We are also schedule the job expire of after 16 hours. No delete the old backups it just expire after 16 hours. We have lots of space so no space issue in db backups
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.