asked
May i know the causes of Transaction logs alway returns fail? why?
May i know the causes of Transaction logs alway returns fail status. we have cancelled the backup of daily.....recently. i searched the information from net ....but it advised that someone broked the chain or bulk insert..... may cause transaction log fails. We didnot do any in server........ Could any one tell me the reason? Could any one give the suggestion on this.........?
Do you mean your transaction log backups are failing? What error do you get?
Or, you're attempting to backup a log but you're getting errors. There are a number of common errors when dealing with backups. I have another article on that right here:
http://www.simple-talk.com/sql/database-administration/7-preventable-backup-errors/ See if one of those is covering your situation.
OK. That's somewhat helpful. What is the error? As I said in my article, there are a number of things it could be, but it really depends on what the error message says.
Right click on the maintenance plan, click view history and expand the failed job, click on the step that is read and copy the text in the error message field, paste it in a new comment. Also you could take a screenshot of the maintenance plan (right click and modify) and post that also.
You're attempting to restore the log on a database, but you're getting errors? Knowing what the error you're getting would make this easier. It could be that when you recovered the full backup you didn't leave the database in a recovering state. You need to use WITH NO_RECOVERY in the RESTORE command. It could be that you're attempting to restore logs that are not correctly linked together. To restore to a point in time you must have logs that are linked. You can look at the log header files and at the Log Sequence Numbers (LSN) to see when one log stops and the next one starts. If there are gaps, that's your problem. Here's an article on how to look at the header information:
http://www.simple-talk.com/sql/backup-and-recovery/how-to-get-information-about-your-backups/ If it's none of these, you need to provide some details as to what error you are getting.
So your transaction log backups are failing? I am going to throw one out there, your transaction log backups are failing because someone added a new database to the server that never had a full backup and your scripts/plans are failing because they are not designed to handle that situation. Think you need to rewrite your post explain what "we have cancelled the backup of daily.. recently" actually means. What is your backup schedule? Are you using built in maintenance plans? Any error message? More info, better help.
"so it may cause this transaction log backup?" What does that mean. Does it mean that it will fail to backup? That it will backup even though you have excluded it? I'm sorry, but this isn't making much sense.
@Rade. You nailed it. Well done. Hopefully @askmtx121 will mark this as the answer because based on the error message, which we finally got, this is 100% correct. Nicely done.
may be some one has change the recovery model of any database from full recovery to simple and change it backup to Full but didnot take the full back after changing the recovery model..
Hi raadee and Grant fritchey, kev riley, **sorry for the late reply bcoz now only i had a chance to took snapshot of error** I have attached the screen shot as you requested..... Transaction log backup every day every hour running but exactly saturday 11pm job failed and next monday 11pm job succeed. between this saturday and monday every 1 hour t-log backup failed continously see the fig for your ref ![alt text][1] **Tlog error history** **Server Name: DevServer** see below: **Job Failed Sample:** Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 01/28/2013 04:00:00,Transaction Log Backups.Log,Error,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job failed. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:05,0,0,,,,0 01/28/2013 04:00:00,Transaction Log Backups.Log,Error,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ... 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:00:00 AM Progress: 2013-01-28 04:00:01.44 Source: {F3A3C8EA-EEA2-44DE-9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-28 04:00:01.72 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-28 04:00:01.72 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-28 04:00:01.72 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 16% complete End Progress Progress: 2013-01-28 04:00:01.72 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create... The package execution fa... The step failed.,00:00:05,0,0,,,,0 01/28/2013 03:34:18,Transaction Log Backups.Log,Error,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job failed. The Job was invoked by User akumar. The last step to run was step 1 (Log).,00:00:05,0,0,,,,0 01/28/2013 03:34:18,Transaction Log Backups.Log,Error,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ... 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:34:18 AM Progress: 2013-01-28 03:34:19.52 Source: {F3A3C8EA-EEA2-44DE-9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-28 03:34:19.80 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-28 03:34:19.80 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-28 03:34:19.80 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 16% complete End Progress Progress: 2013-01-28 03:34:19.80 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create... The package execution fa... The step failed.,00:00:05,0,0,,,,0 01/28/2013 03:27:48,Transaction Log Backups.Log,Error,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job failed. The Job was invoked by User akumar. The last step to run was step 1 (Log).,00:00:08,0,0,,,,0 01/28/2013 03:27:48,Transaction Log Backups.Log,Error,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ... 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:27:48 AM Progress: 2013-01-28 03:27:50.95 Source: {F3A3C8EA-EEA2-44DE-9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-28 03:27:51.51 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-28 03:27:51.51 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-28 03:27:51.51 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-28 03:27:51.51 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create... The package execution fa... The step failed.,00:00:08,0,0,,,,0 01/28/2013 03:00:00,Transaction Log Backups.Log,Error,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job failed. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:05,0,0,,,,0 01/28/2013 03:00:00,Transaction Log Backups.Log,Error,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ... 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:00:00 AM Progress: 2013-01-28 03:00:01.59 Source: {F3A3C8EA-EEA2-44DE-9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-28 03:00:01.91 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-28 03:00:01.91 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-28 03:00:01.91 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-28 03:00:01.92 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create... The package execution fa... The step failed.,00:00:05,0,0,,,,0 01/28/2013 02:00:00,Transaction Log Backups.Log,Error,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job failed. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:08,0,0,,,,0 01/28/2013 02:00:00,Transaction Log Backups.Log,Error,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ... 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:00 AM Progress: 2013-01-28 02:00:02.25 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-28 02:00:02.92 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-28 02:00:02.92 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-28 02:00:02.92 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-28 02:00:02.92 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create... The package execution fa... The step failed.,00:00:07,0,0,,,,0 **Job Sucess sample:** Progress: 2013-01-26 12:00:01.19 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_... The package executed successf... The step succeeded.,00:00:09,0,0,,,,0 01/26/2013 11:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:09,0,0,,,,0 01/26/2013 11:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 AM Progress: 2013-01-26 11:00:00.78 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 11:00:01.05 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 11:00:01.05 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 11:00:01.05 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 11:00:01.05 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_... The package executed successf... The step succeeded.,00:00:09,0,0,,,,0 01/26/2013 10:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:08,0,0,,,,0 01/26/2013 10:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 AM Progress: 2013-01-26 10:00:01.16 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 10:00:01.47 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 10:00:01.47 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 10:00:01.47 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 10:00:01.47 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_... The package executed successf... The step succeeded.,00:00:08,0,0,,,,0 01/26/2013 09:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:10,0,0,,,,0 01/26/2013 09:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:00:00 AM Progress: 2013-01-26 09:00:01.57 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 09:00:01.83 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 09:00:01.83 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 09:00:01.83 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 09:00:01.83 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_c... The package executed successf... The step succeeded.,00:00:10,0,0,,,,0 01/26/2013 08:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:15,0,0,,,,0 01/26/2013 08:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:00:00 AM Progress: 2013-01-26 08:00:00.87 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 08:00:01.13 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 08:00:01.13 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 08:00:01.13 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 08:00:01.13 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_c... The package executed successf... The step succeeded.,00:00:15,0,0,,,,0 01/26/2013 07:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:08,0,0,,,,0 01/26/2013 07:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:00:00 AM Progress: 2013-01-26 07:00:00.81 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 07:00:01.09 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 07:00:01.09 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 07:00:01.09 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 07:00:01.09 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_c... The package executed successf... The step succeeded.,00:00:08,0,0,,,,0 01/26/2013 06:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:49,0,0,,,,0 01/26/2013 06:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:00:00 AM Progress: 2013-01-26 06:00:00.90 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 06:00:01.20 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 06:00:01.20 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 06:00:01.20 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 06:00:01.20 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_c... The package executed successf... The step succeeded.,00:00:49,0,0,,,,0 01/26/2013 05:00:00,Transaction Log Backups.Log,Unknown,0,Devserver,Transaction Log Backups.Log,(Job outcome),,The job succeeded. The Job was invoked by Schedule 26 (Transaction Log Backups.Subplan_1). The last step to run was step 1 (Log).,00:00:25,0,0,,,,0 01/26/2013 05:00:00,Transaction Log Backups.Log,Unknown,1,Devserver,Transaction Log Backups.Log,Log,,Executed as user: Devserver\SYSTEM. ...00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:00:00 AM Progress: 2013-01-26 05:00:01.14 Source: {F3A3C8EA-EEA2-44DE- 9D76-D7167AA92E00} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-01-26 05:00:02.25 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\B".: 5% complete End Progress Progress: 2013-01-26 05:00:02.29 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\C".: 11% complete End Progress Progress: 2013-01-26 05:00:02.34 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'G:\Backups\c".: 17% complete End Progress Progress: 2013-01-26 05:00:02.56 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_c... The package executed successf... The step succeeded.,00:00:24,0,0,,,,0 [1]: /storage/temp/622-tlog+error.jpg
It's still not showing the precise error in this output. Look for a log output from Agent to see if there are better values there. But, on a guess, it looks like you may have been running out of disk space. I only say this because you were getting partial completions on these backups based on the output of 17% complete, etc.
Hi,Grant Fritchy thank you for your immediate response. but we have 100 GB Free space on this specified G:\Backup folder......So drive space is not magor issue i think so ![alt text][1] [1]: /storage/temp/623-tlog+job+agent+fails.jpg
I have given the log recent backup for your ref: any idea? is it relavant to t-log concurrent fail? or backup chain broken? DB NAME Date and time for log backup recent DB1 2013-01-27 01:00:07 DB2 2013-01-27 01:00:10 DB3 2013-01-26 23:00:04 DB4 2013-01-27 01:00:09 DB5 2013-01-27 01:00:10 DB6 2013-01-27 01:00:08 DB7 2013-01-27 01:00:02 DB8 2013-01-27 01:00:04 DB9 2013-01-27 01:00:03 DB10 2013-01-27 01:00:08 DB11 NEVER DB12 2013-01-27 01:00:06 DB13 2013-01-27 01:00:08 DB14 2013-01-27 01:00:06 DB15 2013-01-27 01:00:06
No. The log will be in relatively clear english. Whatever this is, it's a number a date and a time. It's meaningless. If anything it looks like some kind of query against the msdb as to when backups have been run.
Hi used below query to gave this log recent backup date and time ........ SELECT d.name AS "Database", ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup" FROM sys.databases d LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset WHERE type LIKE 'L' GROUP BY database_name,type) b on d.name=b.database_name WHERE recovery_model = 1 AND (backupdate IS NULL OR backupdate < getdate()-1)
Ok, but that doesn't show the error logs for SQL Agent or for your job. My suggestion, go to the SQL Agent job that is running your log backups. Open it up, go to the step that runs the backups and look at it's properties. Go to the Advanced tab. Where it says "Output file:" enter a path and file name and select "Append output to existing file." Now you'll get the full error messages from the output of the T-SQL statements. Since we don't have the actual error, we can't tell you what to do to fix it.
how to Stop t-log contionus fail?
Please, there is a comment button labeled "add New comment" Each of these things you are posting by simply typing into the open text box at the bottom of the page is an answer to your question. Also, you do not need to mark the community edit box on your questions and answers.
how to stop t-log continous fail? any suggestion...........now also it fails.... I already read but cant be come to solution yet.....
http://www.simple-talk.com/sql/backup-and-recovery/how-to-get-information-about-your-backups/
I'm trying to help you answer that, but you just don't have a meaningful error message in any of the things you have posted. I know you said you have plenty of room, but the failures looked like disk space issues. But without an error message saying precisely why the log backup failed, there is no way to tell you what to do to fix it. Do you understand? Think of it like a car. You have a flat tire, but all you keep saying is "My car won't go." I can talk to you about gas, proper compression within your pistons, your radiator, the parking brake, all kinds of things, but without the knowledge that your tire is flat, I can't help you make your car go. Same thing here. We need to identify the error in order to arrive at a solution.
Hi readers, Transaction logs failed on weekends only for following reason Our organisation took database backup on only mon to fri only but t-log backup runs on mon to sun so it runs fine on mon to fri but sat and sun and mon half it throws error bcoz There is no full backup not available for sat and sunday....... ![alt text][1] **My Error:** Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:33:02 AM Error: 2013-01-28 08:33:04.04 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP LOG [ChemStore] TO DISK = N'G:\Backups\storage\db1_backup_201301280833.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'db1_backup_20130128083303', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2013-01-28 08:33:04.04 **Solution:** BACKUP LOG cannot be performed because there is no current database backup This error occurs when the database recovery model has been changed to Simple and/or the recovery model has been changed back to Full but a full backup has not been performed since the change. A full backup must be performed before restarting the backup job. If a full backup has been performed, the following conditions will require that another full backup be taken: · The recovery model had been changed without taking a full backup · A SQL maintenance plan task had changed the recovery model (reindexing can cause this) · A backup specifying TRUNCATE_ONLY had been run on the database · The database was detached from another server and attached to the current server ![alt text][2] **Suggestion:** Took the database backup concurrently relevant to transaction log backup if u understood the **Q & A** give the vote for me and Grant Fritchey....... thanks for Grant Fritchy for valuable guidance........thankxxxxxxxxxxxxxx [1]: /storage/temp/625-transaction+log+failed+reason+on+weekends.jpg [2]: /storage/temp/626-tlog+backup+success+after+db+full+backup.jpg
11 People are following this question.
