May 22, 2012 at 2:31 pm
Ran it again: now log_reuse_wait_desc = NOTHING! Good news!
May 22, 2012 at 2:32 pm
Lynn Pettis (5/22/2012)
Can you confirm that your transaction log backups are running successfully?
Yes, they are. 1 failed at 0030 last night, but they have run successfully since then.
Best wishes
Mark
May 22, 2012 at 2:34 pm
Good, now you should be able to shrink your log to a sensible size.
Doesn't explain what happened though. Are you sure that the log backups were running and were running successfully?
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
May 22, 2012 at 2:35 pm
And the log file is now down to 235 MB, when it was >4GB in size an hour ago!
I think we are cracking this.
Best wishes
Mark
May 22, 2012 at 2:37 pm
MarkThornton (5/22/2012)
Lynn Pettis (5/22/2012)
Can you confirm that your transaction log backups are running successfully?Yes, they are. 1 failed at 0030 last night, but they have run successfully since then.
Best wishes
Mark
I thought you said that was a full backup:
Important piece of information: last night's Full Backup failed, at 00:30 in the morning.
Can you elaborate which backups are on which schedule? I just want to make sure you are on the same page as we are.
Jared
CE - Microsoft
May 22, 2012 at 2:38 pm
MarkThornton (5/22/2012)
And the log file is now down to 235 MB, when it was >4GB in size an hour ago!
What? The actual size of the log file on disk? That won't shrink unless you have a database or file shrink or autoshrink on. Please tell me you don't.
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
May 22, 2012 at 2:38 pm
Is there anything else going on with this database? Is it part of a mirror or anything like that?
May 22, 2012 at 2:41 pm
GilaMonster (5/22/2012)
Good, now you should be able to shrink your log to a sensible size.Doesn't explain what happened though. Are you sure that the log backups were running and were running successfully?
The last failure was at 0030, all have run OK since then, hourly at first, every 15 mins more recently. The total size of the log backup files produced in the last 9 hours is > 24GB, with the largest single one being 4.5GB. That's a lot of transactions per hour!
Ideally, I would like to know why this happened, but I'll probably have to settle for having things return to normality!
Best wishes
Mark
May 22, 2012 at 2:45 pm
Lynn Pettis (5/22/2012)
Is there anything else going on with this database? Is it part of a mirror or anything like that?
No. The only oddity is that a process failed in the website that sits above the database, at about the same time that the weird behaviour started. But no relationship between the two events has been identified yet.
Best wishes
Mark
May 22, 2012 at 2:46 pm
Only thing that comes to my mind at the moment would be one or more long running transactions.
Could be other things, just not sure what at the moment to suggest.
May 22, 2012 at 2:47 pm
GilaMonster (5/22/2012)
MarkThornton (5/22/2012)
And the log file is now down to 235 MB, when it was >4GB in size an hour ago!What? The actual size of the log file on disk? That won't shrink unless you have a database or file shrink or autoshrink on. Please tell me you don't.
I manually shrank the log file, once I realised that it was 90% empty - I know it's not approved practice normally, but the current situation seemed like an exceptional circumstance.
Best wishes
Mark
May 22, 2012 at 2:53 pm
It's fine as a once-off, you just didn't mention that you'd done 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
May 22, 2012 at 2:57 pm
Lynn Pettis (5/22/2012)
Only thing that comes to my mind at the moment would be one or more long running transactions.Could be other things, just not sure what at the moment to suggest.
The database supports a Virtual Learning Environment, and a lot of our students have exams this week - could it be that our usage was genuinely several times greater than normal today, as they are all busy revising? I will check our usage stats tomorrow to see if that might explain things.
Best wishes
Mark
May 22, 2012 at 3:02 pm
MarkThornton (5/22/2012)
Lynn Pettis (5/22/2012)
Only thing that comes to my mind at the moment would be one or more long running transactions.Could be other things, just not sure what at the moment to suggest.
The database supports a Virtual Learning Environment, and a lot of our students have exams this week - could it be that our usage was genuinely several times greater than normal today, as they are all busy revising? I will check our usage stats tomorrow to see if that might explain things.
Best wishes
Mark
I still want to make sure that you are straight on the full backup versus the t-log. You mentioned that last night a full backup failed, but you also referred to that failure as the t-log backup. Note: a full backup does not truncate the t-log. I suppose since you were able to shrink it, something finally backed up the t-log. However, I have to question why that was not working earlier.
Jared
CE - Microsoft
May 22, 2012 at 3:06 pm
SQLKnowItAll (5/22/2012)
MarkThornton (5/22/2012)
Lynn Pettis (5/22/2012)
Only thing that comes to my mind at the moment would be one or more long running transactions.Could be other things, just not sure what at the moment to suggest.
The database supports a Virtual Learning Environment, and a lot of our students have exams this week - could it be that our usage was genuinely several times greater than normal today, as they are all busy revising? I will check our usage stats tomorrow to see if that might explain things.
Best wishes
Mark
I still want to make sure that you are straight on the full backup versus the t-log. You mentioned that last night a full backup failed, but you also referred to that failure as the t-log backup. Note: a full backup does not truncate the t-log. I suppose since you were able to shrink it, something finally backed up the t-log. However, I have to question why that was not working earlier.
Jared,
The full back-up and the t-log backup were both scheduled for 0030 - which might explain why they both failed, although they had both run happily at that time (or near to that time, one after the other) for about a year, without any problems. To be on the safe side, we have now rescheduled the daily full back-up for 0040. Apologies for not making this clear.
Best wishes
Mark
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply