0

Recently I've discovered our 2012 SQL Server database contains over 100,000 Virtual Log files by running the DBCC LOGINFO command. After doing a good deal of research I am still uneasy as to what exact size I should set the Log after shrinking.

With using the code snippet below and based on my details, what suggested size should I set my Log file to and would you recommend that I change my autogrowth?

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO
  • Current Database Size: 39,482,368 KB
  • Initial Database Size: 38,557,000 KB
  • AutoGrowth: 128 MB

  • Current Log File Size: 34,964,480 KB

  • Initial Log File Size: 34,145,000 KB
  • AutoGrowth: 128 MB

A Log backup is taken 8 times a day 6 days a week and Autogrowth is happening pretty much once a week. The Log file is also on a separate storage device from the Database

Also: Many of the VLF's are probably left over from the last DBA not shrinking the file. It has been within a year or two since we updated the autogrowth to 128 MB from the default and set this initial size.

  • I do not understand. The backup log should clear the log. Are you shrinking the log file after each backup and then asking why are you have growth? – JoeDBA_HAHAHA Mar 21 at 14:09
  • Let my clarify. Our TRN Files for the log backups every 2 hours are on average around 20,000 KB but the overall Sql Server Database Transaction log file is 34,964,480 KB which is what continues to grow in size with the autogrowth. The backup is through a maintenance plan, I don't think it is shrinking after each backup. – user3841709 Mar 21 at 14:16
  • So for this exact question I'm wondering if for a one-time shrink and regrow, whats a reasonable size to set my initial size to after I shrink the log so that I don't have autogrowth occuring immediately and frequently – user3841709 Mar 21 at 14:21
  • Correction: the individual backups to appear to shrink automatically its the 34GB database log file that does not – user3841709 Mar 21 at 14:26
1

I think you'll find a good starting point here - Set transaction log size and growth

To summarize from the link:

  • Set your transaction log large enough so it will not grow during normal operation. All update activity stops in the database when the logs grows, so you want to minimize log growths.
  • For an initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
  • Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
  • The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS. You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process. While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
  • In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
  • In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
  • Generally speaking, shrinking the log is discouraged. At some point, there was activity that caused it to grow to that size (index rebuild, etc.). There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size

On a side note, your question states you are taking transaction log backups 8 times per day (6 days per week). I would highly encourage you to read Back Up Transaction Logs Every Minute. Yes, Really.

  • I still have a little confusion in the area of, Since my current Log file is about 35GB should I base the initial size off of that seeing as how it was set at 34GB awhile ago by a DBA (he never shrunk the file) or after I shrink the log I should be looking at a combination of the size of our log TRN files for a week or month and base it off of that? Closer to 35GB or more like 1GB initial size? – user3841709 Mar 21 at 14:44
  • The biggest reason for wanting to shrink the file is to remove those 100,000 vlf's which we believe may be hurting performance – user3841709 Mar 21 at 14:44
  • @user3841709 - can you restore this database to a test server and shrink the log down to something like 10gb - then try rebuilding the largest table/index and note the transaction log growth - that might be the number you shrink your production log down to. – Scott Hodgin Mar 21 at 14:48
  • I can do that, I'll see what results I get if everything goes smoothly – user3841709 Mar 21 at 14:52
  • @user3841709 - you might also consider increasing the log growth value from 128mb to 1gb. The growth event will take longer, but you'll have fewer of them. – Scott Hodgin Mar 21 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.