May 15, 2009 at 4:00 am
All,
I have the following problem on my SQL Server 2005 - 9.00.3042.00 (X64) server.
System description:
-SQL 2005 SP2, build 2042 (X64)
-Database is in Full Recovery mode
-Tran log backup in every 15 minutes
-No replication, no mirroring, nothing "extra"
Usage pattern:
-The system uses small transactions - but once a day there is a bigger one (data load), which bumps up the log usage to 1.5GB
-Normally the log is very rarely used.
-DB data size is 3GB
My problem is that something is not OK with the log file. My findings:
-Putting the DB to read-only mode takes for 10-15 minutes (!), even if noone is connected to the DB
-normally the tran log backup file is 10-12MB, even if no transactions were made between the two dumps
-Loading the backup file (on the standby server) is very slow - it is loading the 10-12 MB backup in Norecovery mode for about 2-3 minutes - the "100%" message appears very quickly ( in 1-2 sec), but then hangs for some minutes.
If I shrink the tran log, then all these problems vanish - log backup sioze will fall down to 80KB, and restore is relatively quick.
I did some investigation:
-HW checked, no problem identified
-CHECKPOINT command cannot help anything
-DBCC OPENTRAN shows no active transactions
-DBCC SQLPERF LOGSPACE shows 1922MB Log size, 26% used (after a log backup!)
-DBCC LOGUSAGE shows only one VLF is in use (!) from the 7708
I don't understand how SQLPERF can show 500MB log usage, while only one VLF is active?
If I do a SHRINKFILE against the log, log usage is dropped to 9MB, small backups, fast "read only" DB option, etc.
I can replicate all these behaviour on other machines as well, using the full backup of this database.
Does anyone has any idea what can be wrong here? It is a magic for me...
thank you!
May 15, 2009 at 4:14 am
Hi,
When ever you do a large transaction (Data Load) each and every entry has to go to Log and then to Database.
If you are running small no of transactions it wont occupy much space in log file. so that is the reason why it is showing less size.
One more point is once the ldf file is grown it will not come down even if you take full backup. You need to use ShrinkFile which you are already using.
Sri
May 15, 2009 at 5:07 am
Thank you,
Of course I know why the LOG was grown.
But how can it happen that
-it says 26% (=500MB) used AFTER a tran log backup, while minimal transaction were processed since
-Only one VLF is in use (less then 1%!)
-Checkpoint has no effect
-I can shrink it to 9MB
This information is inconsistenf for me.
26% LOG used means that 26% of VLFs should be in use (because of any reason), and you could not shrink simply to 9MB.
When I mentioned full backup, I just said that I can reproduce the issue on other machine, so not machine related issue.
May 15, 2009 at 6:17 am
Srikanth Anumalasetty (5/15/2009)
Hi,When ever you do a large transaction (Data Load) each and every entry has to go to Log and then to Database.
Not only a large transaction but each and every transaction has got to be recorded in the tlog if it is in the full recovery model
If you are running small no of transactions it wont occupy much space in log file. so that is the reason why it is showing less size.
Let's put it the other way. Frequent commits does make the tlog reusable.
One more point is once the ldf file is grown it will not come down even if you take full backup. You need to use ShrinkFile which you are already using.
Sri
Yes, once a backup is taken the VLF's are marked as truncated to make it reusable.
May 15, 2009 at 6:22 am
Attila (5/15/2009)
Thank you,Of course I know why the LOG was grown.
Why?
But how can it happen that
-it says 26% (=500MB) used AFTER a tran log backup, while minimal transaction were processed since
-Only one VLF is in use (less then 1%!)
-Checkpoint has no effect
-I can shrink it to 9MB
How did you shrink it?Have you got Autogrowth enabled on your database? May I ask your autogrowth setting as well? And when you said there is a bulk load once a day what recovery model did you set to then? And do you commit frequently in your code? Did you think of changing the reocvery model to Bulk-logged ?
This information is inconsistenf for me.
26% LOG used means that 26% of VLFs should be in use (because of any reason), and you could not shrink simply to 9MB.
When I mentioned full backup, I just said that I can reproduce the issue on other machine, so not machine related issue.
No, its not the hardware issue at all. Its just a smart management of your database, you just need to identify, investigate and troubleshoot it.
May 15, 2009 at 7:47 am
Please try to focus on this:
How can it happen that
-it (DBCC SQLPERF(LOGSPACE)) says 26% (=500MB) used AFTER a tran log backup, while minimal (<100KB) transaction were processed since
-Only one VLF is in use (less then 1%!)
-Checkpoint has no effect
-I can shrink it to 9MB
This information is inconsistent for me.
"
As soon as you can explain it how it happens, it will solve my problem. I'll give a try on SP3
Beleive me, I know what is tlog for, i know that autogrowth has nothing to do with my problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply