Mike,
If you have a long-running transaction that remains open, it prevents the
log space from being reused, which can cause the log file to grow. Log
space can only be backed up and reused back to the oldest still open
transaction in the database. You can investigate using DBCC OPENTRAN to see
if that is the case. (Note that even if the transaction is small, it still
holds the log space prisoner for all the other transactions that run after
it, until it completes.)
Sometimes it can also be helpful to:
SELECT spid, open_tran, last_batch
FROM sysprocesses
WHERE open_tran > 0
ORDER BY last_batch
The last batch tells when batches with open transactions started, but not
necessarily when the current transactions started.
Also, here is Tibor's article on shrinking files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
RLF
<Mike Lamar> wrote in message
news:200911510052mike_lamar@baylor.edu...
> Hello,
> I've got a database, approx 45GB, in full recovery mode. I run nightly
> full backups at 1:00AM and transaction log backups every 4 hours starting
> at midnight. Lately one of the tlog backups has grown to about 80% of the
> database size. The other log backups average 200-500MB each. At first I
> thought the Midnight log backup was not completing before the database
> backup started. I move the midnight backup and confirmed that is is
> completing successfully. The database backup completes successfully as
> well.
> However, now the first log backup after the database backup is having the
> size issue. What impact does the full database backup have on the
> transaction log? Should I run a DBCC SHRINKFILE on the log file after each
> backup?
> Thanks
> EggHeadCafe - Software Developer Portal of Choice
> ASP.NET DATASET Vs ASP RECORDSET SPEED TRIALS
> http://www.eggheadcafe.com/tutorials/aspnet/6dd13594-0a01-4c29-a2fd-0...
|
