May 3, 2006 at 7:03 am
Just curious.....what service pack do you have installed? For SQL Server 2000, it should be SP3a or SP4.
-SQLBill
May 3, 2006 at 8:22 am
I think there is a misconception. Per BOL Simple Recovery Model
"After the log space is no longer needed for recovery from server failure, it is reused."
This doesn't mean the file is shrunk after the process runs. So if the file grows due to a large transaction it will remain that size. The data is marked as committed and those areas of the file can be reused. Truncation is not the same as shrinking the file. It is marking the area as reusable.
For instance when you issue a truncate table of a table which takes up 400GB only the marker is removed and the pages marked as empty. The data still exists on the pages and the file remains the same size. Later transactions will write over those pages as new objects and data are added. As well you can use BEGIN TRAN and ROLLBACK TRAN around a truncate table statement and it appears as nothing happens. Also doing the truncate table seemingly doesn't do anything in the log file because the size doesn't grow to match the missing data. The fact is the truncate table statement is logged.
As for what tools might see, many use methods of reading the log file directly and therefore will see the committed transactions still contained in the log file that have not been written over. So I wouldn't trust what they say.
From BOL
Virtual Log Files
Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files). A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).
Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files. If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files. For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.
As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.
May 3, 2006 at 2:20 pm
Thank you, and that goes for everybody, for taking the trouble to contribute to this thread. However, I do wish people would read it completely.
Yes, I KNOW that the file won't shrink. That's not what I'm talking about (and I've already corrected that same misconception earlier in this thread). I've never mentioned the word "shrink" - all I've ever talked about is "truncate".
I'll recap again:
1. The used portion of the log is not being truncated automatically, even though the database is in SIMPLE recovery mode.
2. The total size of the log remains the same (or grows slightly). I don't care about that.
3. Issuing a TRUNCATE_ONLY backup, or a manual checkpoint, DOES cause the log to truncate (n.b. I do not mean shrink)
4. If I do nothing, then after about a week the log has grown to nearly 50Gb and fills the disk.
5. No other database on this server displays the same behaviour - all other transaction logs truncate normally (n.b. again, I do not mean shrink)
I have been a DBA since 1994 - trust me when I say that I know what the normal behaviour ought to be. The problem I am having is explaining why I have got this one example of very obviously abnormal behaviour.
May 3, 2006 at 2:40 pm
You're absolutely right. I did not catch on to that. With that said. have you tried this.
Detach the db. Rename the log file (we usually change the extension). Use Attach Single File DB to reattach the database and build a new log file.
I have had 3 occasions where the file seemed locked by something else and was not truncating the data simialr to what you state (I was going to include this in my message btw way but wasn't sure if fit). Not sure if it was something hanging out in the virtual logs or the growth setting that was the issue (we knew the logs would grow and were set to 10% originally we change latter to using a fixed 10 MB on most). Anyway, following what I just said to try, corrected the issue in my case. However (and this is not to alarm) after correcting the issue within a month or two each we got consistancy errors on the array and found ourselves with a dead drive quickly. May not have anything to do with what happened with the truncate thing but it is something watch for.
As always make a backup before doing anything on a production server.
May 3, 2006 at 4:57 pm
Thanks Antares - sorry if I seemed to rant slightly, but I just seemed to be going over the same ground several times which was getting frustrating.
I am already planning to try your detach/re-attach suggestion following an earlier post by Tom. You may have noticed from an earlier post that I also said that I now have the identical problem with a database of the same name on a different server. This definitely suggests that there is a corruption in the log of some sort, and destroying it and creating a fresh one might well resolve things. The reason I'm waiting is that this is a production database and getting the necessary downtime (albeit short) to do this isn't straightforward.
The reason I'm persisting with the questions is that I'm very intrigued by what exactly such a corruption might be. If the log simply couldn't be truncated at all, either automatically or manually, then I would be more able to understand it. I'm really intrigued, though, that there is nothing physically preventing the log from being truncated (since I can do that manually using 2 different methods), but instead it just seems to be the automatic truncation that is not working.
I'll keep you posted with any further findings.
May 4, 2006 at 7:41 am
One possibility that I can think of...on this one system are you running database maintenance, such as reindexing or index defragging? Each will cause the log file to increase by huge amounts. If you are doing this on the one machine and not the others, that would explain the difference.
Another....is the autogrow for the log set the same on all of the systems?
I know you've checked to see if there is anything different already, but you might not have considered, or overlooked, those two items.
-SQLBill
May 4, 2006 at 7:42 am
Also, in a previous response I had asked if the SQL Server edition and SP level were the same on all the systems. I didn't see a response. Are they the same?
-SQLBill
May 4, 2006 at 5:18 pm
Hi Bill,
We're only talking about one system here. At the risk of repeating myself, this problem only exists for one database - all other databases ON THE SAME SERVER behave perfectly normally. So, it doesn't really matter what the SP levels are (but to answer your question they're all at SP3a - haven't upgraded to SP4 yet).
To be totally accurate, it actually exists on two systems - there is another server, which again has ONE database behaving abnormally, whilst all the others behave as expected.
I think the clue is that on both servers, the database behaving abnormally has the same name, so I'm working on the theory that either one is a copy of the other, or they are both copies of some other database, and that a corruption in the logfile of the parent database has been inherited during the copy process.
I'm waiting for an opportunity (hopefully this weekend) to completely trash the logs and create new ones as suggested by Tom earlier in this thread.
Regarding the maintenance plans, yes, we do that, but you're missing the point - the log isn't truncating again afterwards. I'm not bothered that it grows - I expect that. I don't, however, expect it to continue growing steadily throughout the week (and if you leave it alone and just monitor it that's exactly what it does throughout the course of a day during normal transaction processing)
May 4, 2006 at 8:02 pm
Got to thinkin earlier and remembered an odd case when we moved a database from SQL 7 to SQL 2000. Was this database moved such? In SQL 7 there was a known issue with virtual logs, they don't collapse easy when trying to truncate them. Sorry I don't have the KB article. Anyway I remember several of the databases continued this behavior in SQL 2000. I don't recall what we did about those and the person who assisted me with the task (overnight tag team) wasn't in today.
May 18, 2006 at 3:44 am
you might like to check this link and patch
http://support.microsoft.com/kb/909369/
just in case the link doesn't work check out the fixes in the post sp4 patch rollup.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2006 at 7:37 am
Colin - thanks for that. I confess I had actually found that reference on Monday, but thanks for pointing it out anyway! (I meant to put a message on here to that effect, but haven't got around to it).
I've decided that I won't apply the hotfix, since we don't have a suitable test environment to try it out on (why no test environment? Let's not go there in this discussion ...!) So, I'm going to hold on until the next service pack is released. In the meantime, I've got a series of scheduled jobs running that explicitly truncate the transaction logs every 15 minutes (all ours databases are in simple mode - we don't need point-in-time recovery).
As an aside, another database on this same server spontaneously started displaying the same errant behaviour last weekend. I've no idea what it is that triggers this, but clearly it can affect a database with no warning. I just find it strange that this hasn't become a major problem for lots of people over the last year or so, since it's a known issue with SP3, 3a and 4. I wonder what it is about a database that means it is going to exhibit this behaviour?
May 18, 2006 at 2:45 pm
I don't usually work with production databases in simple recovery mode to be honest - I'm usually into transactional systems. However I currently do have a few but none refusing to shrink.
It's usually a marker in the log before which the shrink will not pass. I guess the bug allows for a marker to exist which is "false" ( sort of like an open transaction point ) .
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2006 at 3:08 pm
The other thing to remember with this is that BACKUP LOG...WITH TRUNCATE_ONLY is a deprecated command, and MS has said it will not be in the next version after 2005 according to BOL. The stated alternative is SIMPLE mode. So an explicit command to truncate in simple mode is NOT normal behavior; the only reason a log file should grow at all in simple mode is large transactions taking place on the server. In this case the user clearly wasn't seeing that.
May 19, 2006 at 9:18 am
no offence intended Jeff but I think you haven't read the original post fully.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply