November 26, 2007 at 2:00 pm
Quick question for the group. I’m monitoring log usage using:
dbcc sqlperf ('logspace')
The DB is running in full recovery mode. There are no backups running. The application is not rolling any transactions back (this is my performance environment). I would expect the % utilization to only go up as there are no auto grow events but I do see this number drop sometimes?
Could this be a bug or am I just misinterpreting /misusing this measurement?
November 26, 2007 at 2:09 pm
If you've never done a full database backup since switching to full recovery mode, the log will auto-truncate as if it was in simple recovery mode.
Fairly simple reason. Without a full db backup to set the base LSN and to base the log backups from, there's no way to recover to point in time, so keeping the inactive portion of the log is useless. Once a full DB backup gets done, the log will no longer auto-truncate.
If you have done a full DB backup, check that there's no automated job running a backup log truncate or anything like 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
November 26, 2007 at 2:29 pm
Thanks for the info. Yes, thats a very good point. I wasted some time last week on this point. On this occasion I did take a full backup before my test started. Strange.
November 26, 2007 at 2:48 pm
In addition watch out for "autoshrink"/"autoclose" options too.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply