Sql-server – Proper resizing of Log File after shrinking – SQL

sql-server-2012transaction-log

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.

Best Answer

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.

Related Question