SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Odd backup behavior or error?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leoc50
Starting Member

USA
45 Posts

Posted - 08/27/2012 :  16:25:48  Show Profile  Reply with Quote
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

USA
36615 Posts

Posted - 08/27/2012 :  16:31:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 08/27/2012 :  16:39:04  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36615 Posts

Posted - 08/27/2012 :  16:47:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 08/27/2012 :  16:57:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36615 Posts

Posted - 08/27/2012 :  20:13:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
790 Posts

Posted - 08/29/2012 :  13:37:27  Show Profile  Reply with Quote
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.
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 08/29/2012 :  20:56:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000