<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Transaction Log Backup fails on Monday...
32 posts, Page 1 of 4
1
2
3
4
»
»»
Transaction Log Backup fails on Monday Morning
Rate Topic
Display Mode
Topic Options
Author
Message
Judy Scheinuk
Judy Scheinuk
Posted Monday, June 30, 2008 10:45 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, November 25, 2012 10:06 AM
Points: 31,
Visits: 435
Our transaction log backups are failing every Monday morning at the same time. The errorlog reports that there is no full backup on disk, but it is there. The last full backup would have been Friday at 6 PM and that backup file is still there as are the previous 2 full backups. I have to do a full backup - then transaction logs proceed as usual. It appears to me that the backup chain has been broken but I cannot figure out why.
The server is running 9.00.3054.00 SP2 Standard Edition.
This is the error message I receive - database names and server name have been changed to generic names.
Thank you for any light you can shed on this topic.
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054
Report was generated on "SERVER1".
Maintenance Plan: LogBackupPlan
Duration: 00:00:02
Status: Warning: One or more tasks failed..
Details:
Maintenance Cleanup Task (SERVER1)
Task start: 2008-06-30T03:00:02.
Task end: 2008-06-30T03:00:02.
Success
Back Up Database Task (SERVER1)
Backup Database on Local server connection
Databases: model,Database2,Database3,Database1
Type: Transaction Log
Append existing
Task start: 2008-06-30T03:00:03.
Task end: 2008-06-30T03:00:04.
Failed
-1073548784) Executing the query "BACKUP LOG [Database1] TO DISK = N'E:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Database1\\Database1_backup_200806300300.trn' WITH NOFORMAT, NOINIT, NAME = N'Database1_backup_20080630030003', 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.
Post #526100
Lynn Pettis
Lynn Pettis
Posted Monday, June 30, 2008 11:23 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588,
Visits: 27,375
What recovery model is the database using?
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #526111
Judy Scheinuk
Judy Scheinuk
Posted Monday, June 30, 2008 11:30 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, November 25, 2012 10:06 AM
Points: 31,
Visits: 435
It's full. The same maintenance plan for transaction logs runs the rest of the time. I have tried backing up the log manually and that doesn't work either.
Post #526117
anjan.ashok
anjan.ashok
Posted Monday, June 30, 2008 11:34 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139,
Visits: 282
Pls check the path may be their may be the issue.sometimes the log may be full,no space.
Post #526122
Judy Scheinuk
Judy Scheinuk
Posted Monday, June 30, 2008 11:51 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, November 25, 2012 10:06 AM
Points: 31,
Visits: 435
I just checked. There is plenty of space. I don't think it's permissions either since after the full backup, the same transaction log plan works OK. We have had problems with maintenance plans on this server - the integrity checks failed every time with this messages: Failed
0) Alter failed for Server 'Server1' so we wrote our own script for that but I don't really want to write a log backup script. SInce a manual log backup from the query screen doesn't work either, I am not convinced that writing our own script would help.
Post #526131
Lynn Pettis
Lynn Pettis
Posted Monday, June 30, 2008 11:59 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588,
Visits: 27,375
Here is what I think may have happened, and it will require you to run a full backup to restart your backup chain. It sounds like the recovery model may have been changed from Full Recovery to some other model and back again. If this is the case, that could easily be the cause of the log problem.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #526136
Judy Scheinuk
Judy Scheinuk
Posted Monday, June 30, 2008 12:32 PM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, November 25, 2012 10:06 AM
Points: 31,
Visits: 435
I've already run the full backup and transaction log dumps are working again. But your suggestion is very interesting. The errorlog does
show that the server is getting a "backup log with truncate only" but I can't tell what database the messages refer to. There ARE databases on that server in simple recovery mode. When I try to extract the SQL that the maintenance plan is running I never get an answer so I can't see why the maintenance plan is trying to run. I don't think I can run profiler on this server - it is a busy server and the network guys would have a fit. I can't see anything else in the error log or event viewer that would suggest an option has been changed and then changed back. The below lines show a few successful log dumps followed by the unsuccessful one. Our current working hypothesis is "demon possession."
2008-06-29 23:00:05.87 Backup Log was backed up. Database: Database1, creation date(time): 2007/01/09(15:55:26), first LSN: 29541:160:1, last LSN: 29543:256:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database1\Database1_backup_200806292300.trn'}). This is an informational message only. No user action is required.
2008-06-30 00:00:06.52 Backup Log was backed up. Database: Database1, creation date(time): 2007/01/09(15:55:26), first LSN: 29543:256:1, last LSN: 29545:470:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database1\Database1_backup_200806300000.trn'}). This is an informational message only. No user action is required.
2008-06-30 01:00:07.35 Backup Log was backed up. Database: Database1, creation date(time): 2007/01/09(15:55:26), first LSN: 29545:470:1, last LSN: 29548:128:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database1\Database1_backup_200806300100.trn'}). This is an informational message only. No user action is required.
2008-06-30 01:00:28.46 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
Above line repeats many times
2008-06-30 02:00:04.94 Backup Error: 3041, Severity: 16, State: 1.
2008-06-30 02:00:04.94 Backup BACKUP failed to complete the command BACKUP LOG Database1. Check the backup application log for detailed messages.
Post #526152
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Monday, June 30, 2008 12:52 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 4,317,
Visits: 9,216
[quote2008-06-30 01:00:28.46 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
Above line repeats many times
2008-06-30 02:00:04.94 Backup Error: 3041, Severity: 16, State: 1.
2008-06-30 02:00:04.94 Backup BACKUP failed to complete the command BACKUP LOG Database1. Check the backup application log for detailed messages.
[/quote]
Do you have a process scheduled on the weekend to rebuild indexes? Do you have a process that imports data on the weekend (e.g. SSIS package)?
I would bet that you have a scheduled rebuild process that includes the backup log ... with truncate_only after the rebuild operation, or an import process that uses the backup log with truncate_only between import steps.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #526165
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, June 30, 2008 12:52 PM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
Your maintenance plans should be running under specific accounts, likely the Agent account, but you could set it to a particular account and then run Profiler for that particular account. It won't be much of a load if restricted to that account. If the network people are upset, tell them that not being able to recover the database is a bigger problem than a load for a few hours.
If you have databases in simple and full recovery modes, you might need to setup separate plans for them. The databases in either mode shouldn't ever has a backup log with truncate run on them as a regular occurence.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #526166
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Monday, June 30, 2008 1:37 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 4,317,
Visits: 9,216
Steve Jones - Editor (6/30/2008)
Your maintenance plans should be running under specific accounts, likely the Agent account, but you could set it to a particular account and then run Profiler for that particular account. It won't be much of a load if restricted to that account. If the network people are upset, tell them that not being able to recover the database is a bigger problem than a load for a few hours.
If you have databases in simple and full recovery modes, you might need to setup separate plans for them. The databases in either mode shouldn't ever has a backup log with truncate run on them as a regular occurence.
Instead of trying to appease the network people, look at running a server side trace instead. Configure your trace using Profiler, with all of the filters setup and events that you are interested in. Start the run and immediately stop it.
Now, go to File | Export and export the trace definition. Using that file you can setup the server side trace and schedule it to start/stop at a particular time. Set the output file for the trace to the local drive on the server and you should be set.
When you come in next Monday - you will have a trace file that you can load into Profiler, or you can load the data into a table in the database and evaluate it that way.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #526192
« Prev Topic
|
Next Topic »
32 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
