How Would Views & Procs Referencing Another Db Cause Log To Grow?
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.
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.
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?