How would views & procs referencing another db cause log to grow?

Question

Hi,

I've got 2 databases on one server; let’s call them db A and B. Database A is about 11 GB, database B is very small (155mb). Database B has some views and procs which are constantly accessing and updating data in database A.

The interesting thing is, the log for database B quickly grows to huge sizes, I think it grew to about 12 GB in under a week. Database A doesn't grow that big that fast.

What could be causing my log to grow that much? Is there something going on when you select data from another database, that would cause my log to grow like that?

I know when you do something similar against a database on a different server, the data is all moved to the calling database before it's joined and selected ... I can see this causing log growth, could the same thing be happening on the same server?

SQL2000 SP4 STD edition
Full Recovery model

Thanks in advance, John

BTW-I realize that changing my recovery model to Simple may help, but I'd like to know why this is happening first.

Answer 1

I can't imagine why a simple SELECT statement would add anything to the transaction log, but I don't have a lot of experience with linked servers, so I can't say for sure what might be happening behind the scenes.

However, you say "Database B has some views and procs which are constantly accessing and updating data in database A.", so even though the underlying data exists on Database A, because your new application connects to database B and uses it's objects, it seems reasonable that those operations are recorded in B's tran log.

But that's just conjecture on my part. Why don't you just inspect the transaction log directly and see if what it contains seems reasonable? RedGate has a free log explorer that works with SQL 2000 (see http://www.red-gate.com/products/SQL_Log_Rescue/index.htm); I've only used it once before, but it seemed to work well and it really saved my bacon in that particular instance.

I'll admit that I'm curious about the details, but in your situation I'd be tempted to suppress my curiosity and just deal with the log file. After all, this is just temporary, and if the log growth is an artifact of the linked servers it should fix itself once you finish the new app and retire Database A.

Also, Godeke made a good point about backups. If your log file is growing too large, then back it up more often. Backing it up truncates the log, but only internally; i.e. the percentage of the log file used will shrink, but the actual size of the file on disk won't change until you use DBCC SHRINKFILE to do so.