April 19, 2011 at 2:23 am
Hi folks,
I've got a reliable Maintenance Plan that does a full db backup @ 3AM and hourly tran logs. It runs perfectly.
Yesterday the hourly transaction logs failed from 5 PM until the full backup was taken this morning.
SQL log shows
2011-04-18 17:00:34.44 Backup Error: 3041, Severity: 16, State: 1.
2011-04-18 17:00:34.44 Backup BACKUP failed to complete the command BACKUP LOG FAS_LIVE_MPV. Check the backup application log for detailed messages.
Maint plan log shows
Failed: (-1073548784) Executing the query "BACKUP LOG [MyDB] TO DISK = N'F:\\MSSQL\\Bac..." 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.
Nothing in the Windows logs
Now, there was a full backup taken yesterday morning @ 3 AM as usual, and the hourly tran logs ran fine from 4 AM till the failure at 5 PM
There was oodles of disk space free on the backup drive at the time.
I have seen that this error can be triggered when trying to do a tran log backup after changing the Recovery model from Full to Simple - this doesn't apply in this case though.
So ... has anyone else seen this behaviour ?
Any suggestions as to what I should be checking to make sure it doesn't happen again ?
Many Thanks in advance
April 19, 2011 at 2:35 am
Forgot to say, native backups only so the cause isn't as below
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/13e7a89e-bc75-492c-98c6-24e4158f76c3
April 19, 2011 at 3:39 am
Check the SQL error log. Likely someone has switched the recovery model to simple then back to full. Doing so breaks the log chain and no further log backups can be taken until a full or diff backup is taken.
If this is SQL 2005 (I assume not, but just in case), check that no one has run Backup Log ... With truncate_only
Basically, read over the SQL error log between the last successful log backup and now, and see what messages are there. There should be something. If you've not sure, post the messages that are there.
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
April 19, 2011 at 3:54 am
Thanks Gail !
We're using SQL 2008
I don't get any results when I filter the relevant SQL Logs for "Setting database option RECOVERY"
Further weirdness - there's nothing in the log for the failed tran log backups until 9 PM. There should be entries in there for the failures at 5, 6, 7 and 8 PM !
In the screenshot I've filtered for "back"
April 19, 2011 at 4:22 am
Don't filter, you could be missing stuff.
Can you copy out (as text) the error log entries between the last successful log backup and the first failed log backup and post as text (save as text file and attach if it's big). Text so that nothing's cut off and so that I can search through it.
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
April 19, 2011 at 4:51 am
I'll have to warn you - we're auditing successful logins and our main user app connects/disconnects very often so there'll be a good bit of scrolling ...
Anyway, here's the log from the last good tran backup @ 4 PM till the first fail @ 5 PM
<snip Log removed>
April 19, 2011 at 5:29 am
Do you have any history purge job and did the last full backup done successfully
Regards,
Syed Jahanzaib Bin Hassan
MCTS|MCITP|OCA|OCP|OCE|SCJP|IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 5:45 am
Hi Syed,
I don't have a bespoke History Purge job but I do run the standard "Cleanup History" that's part of the maintenance plan.
This runs @ 3 AM though, that was before yesterday's morning full backup.
No jobs ran between the last successful log backup and the first failed log backup.
The Full database backup from yesterday for the databases in question all completed OK.
April 19, 2011 at 6:08 am
First of all apply Service Pack of SQL Server 2008 if you are not using R2,If your Maintenance plan execute on the Integration Service of SQL Server so that could be a problem of connection as mentioned in your log
If Maintenance plan jobs runing on agent service then check service account of agent service it is working or not
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.
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 6:21 am
Joseph Fallon (4/19/2011)
I'll have to warn you - we're auditing successful logins and our main user app connects/disconnects very often so there'll be a good bit of scrolling ...
No worries, I have a SSIS package that loads SQL error logs into a SQL table and remove things like audit success and backup success messages.
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
April 19, 2011 at 6:28 am
From the logs:
2011-04-18 16:15:12.13 spid77 Setting database option RECOVERY to SIMPLE for database MyApp_LIVE_MPV.
2011-04-18 16:15:21.35 spid60 Setting database option RECOVERY to FULL for database MyApp_LIVE_MPV.
Gail's spot on as usual 🙂
April 19, 2011 at 6:40 am
HowardW (4/19/2011)
From the logs:2011-04-18 16:15:12.13 spid77 Setting database option RECOVERY to SIMPLE for database MyApp_LIVE_MPV.
2011-04-18 16:15:21.35 spid60 Setting database option RECOVERY to FULL for database MyApp_LIVE_MPV.
Yup, that'll do it.
A switch to simple recovery breaks the log chain immediately. Any log backup after that fails until another full or diff backup is run.
There was a successful login for the DBA's domain account right before that. Maybe coincidence... 😉
Take a full backup as soon as you can, because until you do you are vulnerable to data loss (no restore will be possible past the last successful log backup). Once you've done that, maybe ask around the people with sufficient access and see if you can find out who did that and why.
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
April 19, 2011 at 7:00 am
Happened on two other db's at the same time as well (each for a very short period):
Line 7417: 2011-04-18 16:12:51.38 spid101 Setting database option RECOVERY to SIMPLE for database MyApp_LIVE_FN.
Line 8471: 2011-04-18 16:13:17.82 spid150 Setting database option RECOVERY to FULL for database MyApp_LIVE_FN.
Line 8951: 2011-04-18 16:13:28.54 spid116 Setting database option RECOVERY to SIMPLE for database MyApp_LIVE_DR.
Line 9444: 2011-04-18 16:13:40.27 spid105 Setting database option RECOVERY to FULL for database MyApp_LIVE_DR.
Line 10004: 2011-04-18 16:15:12.13 spid77 Setting database option RECOVERY to SIMPLE for database MyApp_LIVE_MPV.
Line 10054: 2011-04-18 16:15:21.35 spid60 Setting database option RECOVERY to FULL for database MyApp_LIVE_MPV.
Wonder if this is someone's ill-judged attempt to reduce the Transaction Log size. Time to start the Spanish Inquisition...
April 19, 2011 at 7:34 am
Hey folks,
Thank you so very much for your help.
Minus at least one point for me for not finding that myself :blush:
There is another DBA - I'm going to get my big stick and go a-hunting.
I'd say it was an attempt to clear space somehow.
Best Regards,
Joe
PS I love SQL Server Central !
April 19, 2011 at 7:39 am
Just for the sake of solving the issue here's a script I have built for "emergency" shrinks. The good thing is that you don't have to worry about the log chain with it... just hit execute!
USE [master]
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
USE [PROD-FORDIA]
GO
DBCC SHRINKFILE (N'PROD-FORDIA_Log' , 7500)
GO
USE [master]
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY FULL
GO
EXEC msdb.dbo.sp_start_job @job_name = 'Backup PROD-FORDIA NEW'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply