July 9, 2009 at 9:19 am
I have a database created and used by 3rd party software of which I have no dystem doc. The tlog is currently twice the size of the db. I run weekly fulls, difs on MWF, and tlogs twice a day. There is a shrink in the beginning of the weekly backup job. Implicit is NOT checked in server properties, DBCC OPENTRAN shows no open transactions (although I have one consistent thread (fetch cursor) that does not go away unless I kill it manually and then it just comes back again). I also run a reindex before and after the batch cycle (midnight and 7 a.m.) so that large customers can retrieve large amounts of data via queries otherwise they time out.
The transaction log never decreases in size unless I manually run a DBCC SHRINKFILE command. Is there something I have overlooked or something else that could keep it from recovering space after the backups?
July 9, 2009 at 9:30 am
The only things that reduce the size of the transaction log are DBCC ShrinkFile and DBCC ShrinkDatabase. A log backup will make the space within the log available for reuse, it will not change the size of the file.
If the log is growing then you have some transaction that requires it to be that size. Index rebuilds are a candidate. As a suggestion, run the following before each log back (save the results to a table) and then you'll be able to narrow down when the log grows.
DBCC SQLPERF(LogSpace)
SELECT name, log_reuse_wait_descr from sys.databases
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
July 9, 2009 at 9:40 am
Thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply