Log filling up in Simple Recovery model

Question

Log filling up in Simple Recovery model

Duncan Pryde

SSCertifiable

Points: 7956
More actions
June 21, 2010 at 6:27 am

#103950

Hi
I have a problem with the log file in one of our SQL Server 2000 databases, in that the log space is not being reused, despite the database being in simple recovery mode. As I understand it, when a transaction is finished, the log space should become available again, but this is not happening.
The database is effectively a reporting database that gets updated nightly from another (data processing) database, hence the simple recovery model. The updates are done in small batches of 10000 records precisely in order to stop the log file from needing to get too large. However the used log space is now up to 5GB again and rising daily.
I have another identical database on another server that also gets the same nightly updates. Its log file has remained the same physical size (500MB) and has had over 90% free space for months now.
I've checked DBCC OPENTRAN which tells me I have no open transactions, and DBCC LOGINFO returns 260 rows, the vast majority of which have status 2.
About a month ago when I noticed the log had grown to 60GB, I ran the command ALTER DATABASE <mydatabase> SET RECOVERY SIMPLE, which cleared the transaction log down to over 99% free space, after which it started filling again.
Does anyone have any ideas as to what I should be looking at next in order to find out why the log keeps filling up like it does?
Thanks in advance,
Duncan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could anything be setting the DB back to full recovery? Altering the DB to Simple when its already in simple should do nothing.

Is there replication configured? Does opentran say anything about replicated transactions?

In simple, then a checkpoint runs, the log is marked as reusable to the beginning of the oldest open transaction or beginning of oldest unreplicated transaction.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

Hi Gail

Thanks for replying so soon!

According to both Enterprise Manager and sp_helpdb, the recovery model is simple, so I'm a bit mystified as well as to why running the ALTER DATABASE command seems to work. Thinking about it, I may have altered to full first, then back to simple - I'm afraid I can't remember. Admittedly though, I did only try it that once. I will probably try it again, but wanted to leave it for now in case there is anything that can be gleaned from DBCC LOGINFO or anything else before clearing the log file again.

Regarding replication, the database has never had it set up, and doesn't have it set up now, so I don't think that's the issue either.

I've trawled as much of the internet as I could trying to answer this, but with no joy, so any light you or anyone can shed would be most welcome!

Duncan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If the log starts filling up again, run DBCC OPENTRAN and post the results.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 4

Hi

I've run it just now, earlier today before I posted here, and a month ago when I first noticed the problem and the result is always the same: "No active open transactions."

Regards,

Duncan

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

One thing you can check is whether or not replication was setup on that database. It might have been setup and removed, but not completely.

I also suggest you read the following:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

And this: http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Both of those articles will help you understand how to setup your log file for better performance. Having 260 VLF's is way too many for that size of a transaction log.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 6

Hi Jeffrey

Thanks for the information about vlfs - very useful. I've shrunk the file down and reallocated space for it to remove the disk fragmentation that must have occurred during autogrows. It now has 20 vlfs.

I'm almost completely sure that replication has never been set up on the database, and can't see any evidence that it has, but how could I check definitively?

In order to clear the log to shrink and resize the log file, I tried issuing a CHECKPOINT command, which worked, and left me with only one active vlf. This led me to a BOL page about checkpoints which talks about when checkpoints occur automatically. The bit that drew my attention was this:

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
- The log becomes 70 percent full.
- The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

My database has the recovery interval set to 0, which should mean checkpoints fairly regularly, but it does appear as though automatic checkpoints aren't happening. (Or at least haven't up to now). I'll check over the next few days to see if the reorganizing of the log file has had any effect, but if anyone has any insights in the meantime, I'd love to hear them as always.

Duncan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Duncan Pryde (6/22/2010)
I'm almost completely sure that replication has never been set up on the database, and can't see any evidence that it has, but how could I check definitively?

If DBCC OPENTRAN has any mention of replicated and unreplicated transactions, it means there's remnants of replication. If it doesn't, there's not traces of replication.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 8

Hi Gail

Thanks, in that case there's definitely no replication.

A further search, leading from the checkpoint issue I described above has dug up this hotfix.

I've downloaded the hotfix, and will probably try applying at some point when I can arrange to take the server down temporarily.

Has anyone come across this hotfix before, and had any success with it?

Thanks,

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 9

Ok, an update, so that anyone with the same issue finding this post in a couple of years doesn't get frustrated by the lack of conclusion. 😎

The problem appears to have rectified itself without me installing the hotfix I mentioned in the previous post - on Sunday the log had 70% free space, today (Monday) it has 95% free, without any intervention from me in the meantime. I can only assume that issuing the CHECKPOINT command, followed by shrinking then reallocating space for the log file somehow started it auto-checkpointing again properly. Since I did both, one after the other, I can't be sure which one it was that worked, but if anyone has the same problem it's worth giving them a try.

Thanks to everyone for their help.

Duncan