Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2009-07-27 : 17:37:23
|
| Hi,I have a hourly Transaction Log Backup running in SQL Server 2005 every hour from 8:05am to 8:05pm. Last night the long-running job to back up the .bak job was successful, but started this morning, the Transaction Log Backup was failed every hour.Here is the message of the failed job:Executed as user: ProductionServer\Administrator. ...42.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:05:00 PM Progress: 2009-07-27 17:05:02.22 Source: {1AF55732-79A3-4500-A762-57DDB468D1BE} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-07-27 17:05:02.61 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'C:\Program F".: 100% complete End Progress Error: 2009-07-27 17:05:02.66 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP LOG [dbsclegal01] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Prod01\Prod01_backup_200907271705.trn' WITH NOFORMAT, NOINIT, NAME = N'Prod01_backup_20090727170502', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performe... The package execution fa... The step failed.Can someone tell me what was wrong from this error message. This job has been setup fro more than a year, and just been failed today.Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-07-27 : 22:18:26
|
| Did someone truncate the log chain or switch to simple recovery model? check error log . |
 |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-07-28 : 10:04:39
|
| Thank you sodeep for the response.I didn't change anything yesterday except shrunk the _log.ldf file in the Tasks of database as I fund it was over 6GB which should be around 200MB. The hourly Transaction Log Backup was not running for whole day then. But after a full database backup at midnight, the hourly Transaction Log Backup was running successfully again this morning!Can somebody explain this to me?Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-07-28 : 10:09:28
|
| As I told you, You truncated the log chain with Backup log with Truncate_only. You backup started working after full backup was done. |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-07-28 : 14:46:44
|
| The reason why I shrunk the database was because I also setup an Alert in SQL Server Agent.The Options is "Include alert error text in: 'E-mail'". The Counter is set to "Percent Log Used".Alert if counter is set to "rises above". And Value is set to "95%". So, please tell me what is the best way to reduce the Value when I get an email from this alert. Why I have the Transaction Log Backup running every hour and the _log.ldf still goes up to 6GB? Clearly, if I didn't shrink the database, the _log.ldf would stay over the 6GB, and I would keep getting emails from that alert. What is the way to keep the _log.ldf in 200MB level and what is the solution when I get the email from the Alert?Thanks. |
|
|
|
|
|
|
|
|
Hourly Transaction Log Backup failed