February 21, 2009 at 7:24 am
I run a dual 24x7 database system. It's accessed by 15-20 services inserting records into 2/3 individual databases on both systems. Additionally, 300 or so users look up information from the system, but they do pretty much no data entry.
Recently we have upgraded to SQL 2k5 SP2.
I do index rebuilds overnight at the weekend to minimise disruption to the main service. I've split them across the big databases. This works fine apart from one database, which fills the log with status 2 files. This is consistent across both systems and does not clear down (I left it for a week at one point). I've tried "ALTER TABLE .. REBUILD .. ONLINE = ON", "ALTER TABLE .. REBUILD .. ONLINE = OFF", and "DBCC DBREINDEX .." with no change in behaviour.
I've been clearing this status 2 files out but want to know why this is happening after the upgrade? Why it's only one database out of three that are active?
I don't have replication running, and know that isn't the problem. Normally, the databases run in full recovery model.
So, any guesses, SQL experts?
February 21, 2009 at 7:29 am
Richard (2/21/2009)
This works fine apart from one database, which fills the log with status 2 files.
Status 2 files? Not sure what you mean. Clarification please?
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
February 23, 2009 at 3:24 am
Sorry, been doing non-computer things!
"Status 2 Files" are rows within a DBCC LOGINFO command that have a status of 2, and as such do not clear down. As described in http://qa.sqlservercentral.com/Forums/Topic175822-5-1.aspx, but there are no entries from a DBCC OPENTRAN command.
February 23, 2009 at 7:20 am
Ok, three steps back...
What's the problem, why are you worried and what issues are these 'status 2' files causing?
How many log files do you have, how many database is there a problem with?
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
February 23, 2009 at 7:49 am
Hi,
Thanks for your replies, I appreciate you taking time on this.txtPost_CommentEmoticon(':)');
The issue is three-fold.
First, it eats diskspace for backups. Because the log fills with these "Status 2" files, the transaction log backup becomes huge, about the same size as the main database backup, at 120Gb-ish. I'm running with an hourly transaction log backup (with daily database backup) which means it eats diskspace. I'm trying to keep 3 days of backups which would require over 8Tb at that rate. I'd normally expect these backups to not be more than 100Mb each, or 0.1% of the bloated size, which then easily fits into 400Gb for 3 days when including the daily database backups.
Second, I'm a tad concerned that these "Status 2" files indicate that something is genuinely not completing, either a process or the re-indexing. I feel it probably is, but just trying to assess what is happening/not happening would ease my mind. However as I say, the DBCC OPENTRAN does not show anything open.
Third, I don't like having to clear out the transaction log. Switching to "Simple" Recovery Mode, dumping the transaction log, and switching back to "Full" just feels wrong! I'm concerned that if something happens at that time, despite full backups before and after, I'm not keeping the database in the proper manner.
The system is a mirrored instance across 2 physical sites. Each instance of SQL Server has 7 databases on it, of which about 3 are fairly active. Only 1 database on each site, which does the same job, throws up the problem. The other two seem to manage fine through the re-indexing process.
Cheers
Richard
February 23, 2009 at 8:03 am
Ok, so let me see if I understand (ignoring details of virtual log files and statuses, which is just complicating the issue)
Your log file is constantly growing, despite running regular log backups?
Your log backups are huge, far bigger than they should be?
OPENTRAN indicates no open transactions?
The log is full (Check DBCC SQLPERF (LOGSPACE) to see the utilisation of the log file)
Is that about right?
What exactly does DBCC OPENTRAN return?
What does DBCC SQLPERF (LOGSPACE) return?
What's the Log_Reuse_Wait_Descr for this database (query sys.databases)
What's the recovery model and how often are you doing log backups?
Any errors in the SQL error log?
Are the log backups succeeding? What's the exact command you're using to backup the log?
Do you have database mirroring? What mode?
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
February 23, 2009 at 8:44 am
Answering in order.
The log file for this one database does grow huge, only when running a RE-INDEX. After I've cleared the log file down (by switching to "Simple" Recovery Model, dumping the log, then switching back to "Full"), it behaves normally. The regular log backups are very small in comparison and vary up and down, suggesting that the log does clear itself out during normal operation.
OPENTRAN does indicate no open transactions.
When doing DBCC SQLPERF (LOGSPACE) after a RE-INDEX the log file is full. After clearing it out, it behaves normally as well.
I think the only thing that is different to you original comments is that the log file is not growing all the time, just during a RE-INDEX and then doesn't clear down.
DBCC OPENTRAN returns "No active open transactions.".
DBCC SQLPERF (LOGSPACE) returns 99%-ish after a RE-INDEX, but after clearing the log around 1% and stays around there until the next RE-INDEX.
I'll look at the Log_Reuse_Wait_Descr when I'm next in the situation. I didn't look at that beforehand and have cleared down the log now.
I use "Full" recovery model and do log backups every hour.
There are no errors in the SQL error log.
The log backups do succeed but are huge after a RE-INDEX and before I clear down the log.
The command to backup the log is "BACKUP LOG [ WITH NO_TRUNCATE".
I don't have database mirroring.
February 23, 2009 at 8:54 am
Richard (2/23/2009)
The log file for this one database does grow huge, only when running a RE-INDEX. After I've cleared the log file down (by switching to "Simple" Recovery Model, dumping the log, then switching back to "Full"),
Are you aware that breaks your recovery chain? You'll be unable to do a point-in-time restore any time after that until you do another full backup.
The command to backup the log is "BACKUP LOG [ WITH NO_TRUNCATE".
There's your problem. You're telling SQL not to clear the log when it backs it up. Why are you backing up with no_truncate?
Remove the no_truncate clause from your log backups. That will ensure that log backups do clear the inactive log records, as they should.
Index rebuilds are intensive and they will use up the log. That's to be expected. Size the log so that is can handle the index rebuilds adn don't shrink it. It will just grow again.
Don't switch to simple and back unless you're happy with not been able to recover any time after that.
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
February 23, 2009 at 9:17 am
Breaking the recovery chain was the thing that made me a bit jumpy. I do a full backup directly after finishing this job to start the chain back up again.
I have to say that I saw the "no_truncate" option just now as well. Hmm, I do wonder why I've got that in (set it up a good 18 months ago!). There may have been reasons back then.
What confuses me now is that they ever clear down, and they do. Hmm, at least this may sort this out now. Thanks! As previously said, much appreciated, and I feel a tad guilty for such an obvious fault, even if I'm confused is to how it's been working in the way it has up unto now.
If it does clear down the log, and there's no reason to doubt it, even with so-named "Status 2" files, then I guess I don't worry about it.
Indeed, Index rebuilds are very intensive, the biggest thing we do. I used to shrink the log after rebuilds because it was so much a bigger job than anything else and gave me a bit more diskspace to play around with in the intervening week. It's fine to re-grow when it needs it the next week, although I know how intensive that is. I've recently decided I don't need the diskspace and will leave it to this process.
Anyway, thanks again for your patience and help.
February 23, 2009 at 9:25 am
Richard (2/23/2009)
What confuses me now is that they ever clear down, and they do.
There may be another process on another schedule that doesn't have the no_truncate, or maybe even one with truncate_only. Check all your jobs?
If there's still an issue, check sys.databases
I used to shrink the log after rebuilds because it was so much a bigger job than anything else and gave me a bit more diskspace to play around with in the intervening week. It's fine to re-grow when it needs it the next week, although I know how intensive that is.
It's not just the intensiveness of the growth that's the problem. Repeated shrinks and grows cause internal and external fragmentation, even in log files
You could always switch to bulk-logged recovery before doing the index rebuilds. Rebuild is a bulk operation.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply