July 21, 2011 at 11:18 am
This is a SQL 2005 Server.
OK, here's what I have. 2 Jobs....
BackupDB_Full: This is nightly at 8:30PM
BackupDB_TX: This is on the hour every hour.
The full backups run fine and all within 15 minutes or less so theoretically it will never hit or go past 8:59pm. At least the history shows it never went that long.
The hourly TX backup runs then suddenly it stops with the below error.....
Failed:(-1073548784) Executing the query "BACKUP LOG [My_DB] TO DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\My_DB\\My_DB_backup_201107210900.trn' WITH RETAINDAYS = 5, NOFORMAT, NOINIT, NAME = N'My_DB_backup_20110721090002', 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.
I imeediately keyed in on "because there is no current database backup."
So I checked to see if the backup file exists.......and it does. It's the backup of the 8:30PM schedule.
For kicks, I manually did a full backup of "My_DB" and waited for the next hourly trigger time for the TX backup. It worked!
So I figured cool, it should run fine and the next Full DB backup will run and it should run fine after that right?
WRONG!.....lol :w00t:
It (TX backup job) worked after a few hours from the normal scheduled full backup then for some funky reason the same error popped up again. Another manual full db backup fixed it again but it's going to fail again :crying:
Any ideas where to look/change?
July 21, 2011 at 12:05 pm
Something's either running Backup log with truncate_only or switching the DB to simple recovery and back to full. Check jobs, check the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 12:42 pm
I hope not!
I did notice a trend that it fails at a specific time. At least the past 2 days it did. There is only one job that runs at that specific time and all it does is qureies an ADSI linked server.
July 21, 2011 at 12:54 pm
If the log backup is failing with 'no existing database backup' it means that either backup log with truncate only has been run or that the recovery model has been switched to simple and back. It's not a case of hoping, those are the things that cause that error.
Check the error log, all recovery model changes are logged. I can't recall if backup log with truncate only is logged or not, I have a feeling that it is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 1:42 pm
Yes, backup log with truncate only is logged. I see it. There's actually a job that does this just prior to the FULL db backup.
At least the job logs it anyway.....lol
OK, so the "Chain" for backups seem to be OK because a FULL is done right after and an hourly TX is run after that and it works consecutively on the hour.
But strangely when 5AM hit's, the chain get's broken. I couldn't find anything in the logs that shows a change from Full to Simple backup mode in corellation to around/between 4-5AM. Is that logged? If I recall in the waaay past I think I saw that on a test server.
I also checked the SP level and it's on SQL SP2. I'll try going to SP3 later and see what happens. I have to wait till this weekedn because this is a production server.
Thanks for your help Gail!
July 21, 2011 at 2:22 pm
Warren Peace (7/21/2011)
Yes, backup log with truncate only is logged. I see it. There's actually a job that does this just prior to the FULL db backup.At least the job logs it anyway.....lol
OK, so the "Chain" for backups seem to be OK because a FULL is done right after and an hourly TX is run after that and it works consecutively on the hour.
That's still a very bad thing to do, as it means that if a full backup is not restorable you have no options for restoring to point of failure. Take that out of the job, truncating a transaction log is very bad mismanagement
But strangely when 5AM hit's, the chain get's broken. I couldn't find anything in the logs that shows a change from Full to Simple backup mode in corellation to around/between 4-5AM. Is that logged? If I recall in the waaay past I think I saw that on a test server.
Yes it is.
I also checked the SP level and it's on SQL SP2. I'll try going to SP3 later and see what happens. I have to wait till this weekedn because this is a production server.
Not going to change anything.
I'd stick profiler on and see what it catches in between those times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 2:48 pm
GilaMonster (7/21/2011)
That's still a very bad thing to do, as it means that if a full backup is not restorable you have no options for restoring to point of failure. Take that out of the job, truncating a transaction log is very bad mismanagement
I agree. But when the drives fill up because the TX log files of one (or many) of the DB's TX bloats to 0MB left on the hard drive and applications stop beacuse they can't write (record?) data, you have to decide which is worse? All 800 users offline till you can either VPN or speed into work (while your "meanager" is on the phone) or not being able to restore or playback the last recorded TX backup file at hour 11 just prior to a Full backup.
Correct me if I am wrong but, if I backup a DB's TX at 7:45PM then truncate, then do a FULL of the DB at 8:00PM doesn't that give me (record?) all the TX but the last 15minutes of transactions (if any during non biz hours) and since SQL is autocommit the FULL should catch the last 15minutes of data transacted?
I've been in the full drive scenario. Not fun. I've also been in the play transactions forward scenario. Tedious and not fun either.
I'm in Government so we are totally broke so adding more drive space is like asking for a raise.
I'll profile for "ALTER".
July 21, 2011 at 2:57 pm
Warren Peace (7/21/2011)
I agree. But when the drives fill up because the TX log files of one (or many) of the DB's TX bloats to 0MB left on the hard drive and applications stop beacuse they can't write (record?) data, you have to decide which is worse?
Truncating the log right before the full backup won't stop that.
All you're doing by truncating right before the full backup is breaking the log chain and then immediately restarting it. It's not going to stop the log growing before the truncate point, it's not going to stop the log growing after the full backup.
If you want to keep the log managable, back the log up regularly and make sure there are no other problems that cause the log to grow (failed replication, open transactions, etc)
Correct me if I am wrong but, if I backup a DB's TX at 7:45PM then truncate, then do a FULL of the DB at 8:00PM doesn't that give me (record?) all the TX but the last 15minutes of transactions (if any during non biz hours) and since SQL is autocommit the FULL should catch the last 15minutes of data transacted?
Sure, but if that full backup is damaged and not restorable you can no longer take the previous full backup and 2 days of log backups and restore to point of failure. The break in the log chain will prevent that.
I'll profile for "ALTER".
Also BACKUP.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 3:14 pm
GilaMonster (7/21/2011)
If you want to keep the log managable, back the log up regularly and make sure there are no other problems that cause the log to grow (failed replication, open transactions, etc)
Hmmm....
You triggered a memory and I think this truncate job was to relieve disk space contension because of "failed replication, open transactions, etc".
I think i'll disable it and see what happens or doesn't happen. I'm pretty sure the issue was fixed but can't remember.....getting old sucks.
Sure, but if that full backup is damaged and not restorable you can no longer take the previous full backup and 2 days of log backups and restore to point of failure. The break in the log chain will prevent that.
How does one ensure that all ~70 DB backups on a SQL server are not corrupted? I understand your point but there's over 70 DB's on there and that's just one server. Myself and one other person manage over 28 SQL Servers. We do audit some and some we get requests to copy Production DB's to Dev servers. But there's now possible we we can "Test" that all the full backups are not corrupt in one day.
BTW, I was going to post that as another question in the Forum. 😀
Also BACKUP.
Thanks! I'll add that.
July 21, 2011 at 3:30 pm
Warren Peace (7/21/2011)
GilaMonster (7/21/2011)
If you want to keep the log managable, back the log up regularly and make sure there are no other problems that cause the log to grow (failed replication, open transactions, etc)Hmmm....
You triggered a memory and I think this truncate job was to relieve disk space contension because of "failed replication, open transactions, etc".
It won't do either though. Backup log with truncate only will only mark the log as reusable if a normal backup log would mark the log reusable.
All it does is what a normal log backup does, except that it discards the log records instead of writing them to the backup file and it marks the log chain as broken
Sure, but if that full backup is damaged and not restorable you can no longer take the previous full backup and 2 days of log backups and restore to point of failure. The break in the log chain will prevent that.
How does one ensure that all ~70 DB backups on a SQL server are not corrupted? I understand your point but there's over 70 DB's on there and that's just one server. Myself and one other person manage over 28 SQL Servers. We do audit some and some we get requests to copy Production DB's to Dev servers. But there's now possible we we can "Test" that all the full backups are not corrupt in one day.
I wasn't suggesting you test them all (but you should have automated test restores).
Let's consider this situation.
Sunday 10pm full backup
Log backups every hour
Monday 10pm full backup
Log backups every hour
Database fails spectacularly at 3pm on Tuesday.
I go to restore the Monday night backup. It's damaged and not restorable
I restore the Sunday night backup. It restores fine.
I then restore all the log backups from Sunday night through to Tuesday 3pm, recovering the database with no data loss.
Now your situation
Sunday 10pm full backup
Log backups every hour
Monday 9:45pm Backup log with truncate only
Monday 10pm full backup
Log backups every hour
Database fails spectacularly at 3pm on Tuesday.
I go to restore the Monday night backup. It's damaged and not restorable
I restore the Sunday night backup. It restores fine.
I then restore all the log backups from Sunday night through to Monday night 9pm. I can't restore any log backups past that point, because the log chain was broken by the log truncation.
Data loss = 18 hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 4:36 pm
GilaMonster (7/21/2011)
Let's consider this situation....
Now your situation
And that's the gamble.
In the pleasant scenario you give there's a chance (it happened more than once) that the one, or many, TX log files bloat and ALL the DB's stop recording data and TX's stop which means all the applications for 800 (actually there's over 2000 but let's be real, were govt so...) users are "Down" and it’s server wide. Let's say it took me 45minutes to excuse myself from a meeting offsite, drive in and shrink the log files. That's (800 users * .75 hours) = 600 hours lost. Now multiply that times their wages and that's what management will hit the DBA with.
In my scenario it's a single DB that’s affected.
Now the DB in question usually has less than 20 users "Connected". What the transactions are who knows or are they doing anything? So sure that single DB goes down, 20 users are down for 45 minutes which means 12hours was lost. Now multiply that times their wages and that's what management will hit the DBA with.
What you presented as my situation is essentially quantifyably the same compared to the 600hours of lost time.
I can't control (to a certain extent) what the users are doing or what the "Canned" applications are doing but I can slap the TX log file(s) back down to prevent the TX file growth to reduce lost worker times server wide.
It's almost like voting, you have to pick the least of 2 Evils. 😛
July 21, 2011 at 7:45 pm
Warren Peace (7/21/2011)
In the pleasant scenario you give there's a chance (it happened more than once) that the one, or many, TX log files bloat and ALL the DB's stop recording data and TX's stop which means all the applications for 800 (actually there's over 2000 but let's be real, were govt so...) users are "Down" and it’s server wide.
Truncating the transaction log right before a full backup will not prevent that. It'll do no more to 'prevent' the log bloating than a normal, stock-standard log backup to disk.
Backup log with truncate only does nothing more than a normal log backup does, it just doesn't write the log records to a backup file. It does mark the log chain as broken, but the full backup immediately restarts the log chain.
It will not prevent the log from growing due to active transactions. It will not prevent the log from growing due to replication. It won't even prevent the log from growing due to needing a log backup since there's a full backup straight afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2011 at 2:23 pm
GilaMonster (7/21/2011)
Let's consider this situation.
Sunday 10pm full backup
Log backups every hour
Monday 10pm full backup
Log backups every hour
Database fails spectacularly at 3pm on Tuesday.
I go to restore the Monday night backup. It's damaged and not restorable
I restore the Sunday night backup. It restores fine.
I then restore all the log backups from Sunday night through to Tuesday 3pm, recovering the database with no data loss.
Now your situation
Sunday 10pm full backup
Log backups every hour
Monday 9:45pm Backup log with truncate only
Monday 10pm full backup
Log backups every hour
Database fails spectacularly at 3pm on Tuesday.
I go to restore the Monday night backup. It's damaged and not restorable
I restore the Sunday night backup. It restores fine.
I then restore all the log backups from Sunday night through to Monday night 9pm. I can't restore any log backups past that point, because the log chain was broken by the log truncation.
Data loss = 18 hours.
Very Good Example
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply