June 30, 2008 at 10:45 am
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.00SP2Standard 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.
June 30, 2008 at 11:23 am
What recovery model is the database using?
😎
June 30, 2008 at 11:30 am
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.
June 30, 2008 at 11:34 am
Pls check the path may be their may be the issue.sometimes the log may be full,no space.
June 30, 2008 at 11:51 am
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.
June 30, 2008 at 11:59 am
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.
😎
June 30, 2008 at 12:32 pm
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.
June 30, 2008 at 12:52 pm
[quote-02008-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-0]
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2008 at 12:52 pm
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.
June 30, 2008 at 1:37 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2008 at 1:38 pm
OK, now I have to think about this. The weekly maintenance plan does run at the right time to mess things up but I don't see anything suspicious in there - certainly no commands to dump the log with truncate only. But in further poking around, I see that there is a linked server that is probably the vendor's and I am starting to wonder if THEY issue any commands. I am going to try to find out something about this linked server. It will probably take a while.
July 7, 2008 at 5:49 pm
We have identified our problem. A purge is being run through the application on Monday mornings at 1:00 AM. The person in charge of the application is checking to see if there are any options either to make the purge be a logged operation or to dump the database afterwards. If not, we will schedule a full dump for shortly after the purge.
Thank you for your suggestions. I was stuck because I knew we were not doing anything to cause the problem and I didn't even think that it could have originated from outside SQL Server.
July 7, 2008 at 8:44 pm
Judy Scheinuk (7/7/2008)
We have identified our problem. A purge is being run through the application on Monday mornings at 1:00 AM. The person in charge of the application is checking to see if there are any options either to make the purge be a logged operation or to dump the database afterwards. If not, we will schedule a full dump for shortly after the purge.Thank you for your suggestions. I was stuck because I knew we were not doing anything to cause the problem and I didn't even think that it could have originated from outside SQL Server.
Judy, I am not sure I understand the comment about making the purge be a logged operation. If the application is purging data (e.g. DELETE FROM table WHERE someconditions), then that operation is already fully logged in the transaction log.
I would guess that the purge process includes a BACKUP LOG ... WITH TRUNCATE_ONLY to try to minimize the log usage - however, this would only be valid if they are deleting in batches (i.e. delete 1000 rows, issue backup with truncate_only, delete another 1000, etc...).
Either way, once the log has been truncated - the log chain has been broken and you have to perform a full backup. Another option would be to perform the purge as:
1) Backup transaction log and modify recovery model to simple
2) Run purge in batches
3) After each purge operation, perform a checkpoint
4) Modify recovery model to full and then run a full backup
Please be aware that during the above operation, you have no ability to recover to a point in time after the process starts until the process completes. So, if the process takes several hours and the system crashed during the process you could lose up to several hours of data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2008 at 9:53 pm
Well, the problem is that we don't have much control over how the application does the purge. That is what we are looking into now. I realize that if it is simply doing a "Delete from" it would be logged. I can't imagine that they'd be truncating any tables. You are probably right that they truncate the transaction log at some point. The messages in the error log suggest that that is what is happening. I guess one option would be to take over the purge and do it through SQL so we could back up, update statistics and otherwise tidy up the tables. That might be the best option. I think they are just clearing out old data by date. Thanks for your thoughts. The vulnerability for data loss makes me think that either they need to manage to do the purge without the truncate log or we need to take it over - either way could preserve the backup chain. I'd really like to have a full backup taken after a purge anyway. Thanks again -
Judy
July 8, 2008 at 9:51 am
check the AUTO CLOSE and AUTO SHRINK features in the database..
TURN off these 2 Features and check again.
Maninder
www.dbanation.com
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply