Odd backup behavior or error? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Odd backup behavior or error?

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-08-27 : 16:25:48

I wonder if anyone has faced this situation before:
A server with a maintenance plan for Full backups for all 32 DBs once/day and another for Tlogs hourly also all DBs.
Full backup runs at night (9:00p) no errors/messages, in fact I can see the entry in the log saying it completed all databases.
Maintenance plan backups for Tlogs runs also with no errors ALMOST all the time, I just discover that every 3 weeks (on Sundays at 12:00am) the Tlog plan fails on just one (1) database. It keeps failing (without any intervention) until the next sched. full backup finishes. "for political/policy reasons I can't 'fix' these things outside business hours...long story not relevant here"
I can't find any hint or info (on the server) that might pertain to this situation.
Most info on the web seem to believe their issue has/was to do with 3rd party backup tools, I'm using plain simple maintenance_plans.

Thanks!
leo

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-27 : 16:31:02

You'll need to locate the error for that failed backup in order for us to help. It'll be in the SQL Server Error Log.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-08-27 : 16:39:04

Job_Log:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4053
Report was generated on "BP1DRJDB001".
Maintenance Plan: DBA - Backup Transaction Logs
Duration: 00:00:09
Status: Warning: One or more tasks failed..
Details:
Back Up Database Task (BP1DRJDB001)
Backup Database on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2012-08-12T10:00:32.
Task end: 2012-08-12T10:00:40.
Failed:(-1073548784) Executing the query "BACKUP LOG [SWHSystemJournal] TO DISK = N'M:\\MSSQL.1\\MSSQL\\backup_tran_logs\\SWHSystemJournal\\SWHSystemJournal_backup_201208121000.trn' WITH NOFORMAT, NOINIT, NAME = N'SWHSystemJournal_backup_20120812100032', 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.

Extract/sample from the ERRORLOG (as I said every hour on the hour):
2012-08-26 02:00:38.83 Backup Log was backed up. Database: SWHSystemAudit, creation date(time): 2011/05/27(16:51:51), first LSN: 975:1092:1, last LSN: 975:1092:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'M:\MSSQL.1\MSSQL\backup_tran_logs\SWHSystemAudit\SWHSystemAudit_backup_201208260200.trn'}). This is an informational message only. No user action is required.
2012-08-26 02:00:38.92 Backup Error: 3041, Severity: 16, State: 1.
2012-08-26 02:00:38.92 Backup BACKUP failed to complete the command BACKUP LOG SWHSystemJournal. Check the backup application log for detailed messages.
2012-08-26 02:00:39.19 Backup Log was backed up. Database: WAMS, creation date(time): 2011/05/21(10:30:04), first LSN: 488:385:1, last LSN: 488:385:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'M:\MSSQL.1\MSSQL\backup_tran_logs\WAMS\WAMS_backup_201208260200.trn'}). This is an informational message only. No user action is required.

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-27 : 16:47:27

It sounds like someone is truncating the log which is breaking the transaction log backup chain. So in order to get around this error, a full backup or a differential has to be run.

But you need to track down why the tlog chain is breaking for this database. Stop that process.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-08-27 : 16:57:30

Thanks Tara.
How can one truncate the log?
This usually starts at midnight (server is in Brazil) on Sundays (last 3 times I can tell so far). I suspect no one is logged in besides as far as I can tell the only people with higher than 'dbo' are not using this system.
So no 'magic' number reached by 32 DBs in on plan, i thought it was curious!
thanks for further ideas/hints!
leo

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-27 : 20:13:15

BACKUP LOG ... WITH NO_LOG is how it would happen. Unless that error message means something else (sometimes MS does that to us), the tlog chain is broken until the full backup runs. You could run a script that queries sys.databases every few minutes, and the output when you encounter this issue. Or you could run a trace to see what's going on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-08-29 : 13:37:27

The log chain can also be broken by changing recovery model to simple and back to full. You should be able to find either of these in the SQL Server log if they are happening.

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-08-29 : 20:56:45

That seems to me is more of this case "...sometimes MS does that to us..." I am sure going to be looking the article (is yours right, and your blog; i used to browse thru it couple years ago, very good tips! thank you for that!!!)

I can see how changing the model would also break the chain, but not this case,

thanks for the input you guys!!!

Leo

- lec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources