July 19, 2012 at 8:57 am
I am at a total loss here... :ermm:
Yesterday I noticed my log file was larger than my database. I backed up my log file and ran DBCC SHRINKFILE and everything was fine. It set the initial size to 32mb on the database properties and performance was hugely increased. Less than 8 hours later, the log file was set to an initial size of 6GB and, of course, the log file was 6GB. After running DBCC LOG, I saw the log file had only 4000 rows. There's no way that 4000 rows = 6GB.
Why or what could be causing my log file to be set to 6GB?
(Additional Info: DB = FULL recovery. Maint. Plan run every night to rebuild indexes. Prior to Maint. Plan, alter script run to change recovery mode to BULK-LOGGED. The alter script run again to revert DB back to full. I can verify in the logs that alter db done, maint plan run, alter db back done.)
July 19, 2012 at 9:16 am
try running these commands
SELECT name,log_reuse_wait_desc
FROM sys.databases
DBCC loginfo
DBCC SQLPERF (logspace)
they will if you log reuse is waiting on something, you have many VLFs, or lots of unused space on the transaction log respectively. If you have lots of unused space you might want to try backing up the transaction log more frequently. If you are already backing up trans log on a regular basis then I would check for open transactions.
DBCC opentran
here are some good blog posts by Kimberly Tripp about transaction log maintenance
http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply