VLF, Shrink and Simple Recovery

Question

We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem.

My question is shrinking will only reduce from the end of the file to the last active portion of the log. Therefore should I change to simple recovery first, then perform the shrink?

Following this I will change back to full perform a full backup to start the backup chain again.

Answer 1

No, do not switch to SIMPLE recovery model since that will result in a break in your backup chain. If you are doing in FULL recovery model, do the following:

1. CHECKPOINT
2. BACKUP the LOG
3. DBCC SHRINKFILE (logfile, targetsize)

Repeat until you shrink the log file as small as possible. (My experience is that two or three times is usually needed.)

Then set the preferred default log size and the increment of growth for the log. Do not use percentage log growth, but have a defined increment.

If you follow this, then the backup chain is preserved.

If there are reasons that you need to switch to SIMPLE recovery model, then once you return to FULL recovery model you should BACKUP DATABASE to reestablish the start point for your following log backups.

Answer 2

Since you mentioned about lot of evil things you are going to do in your database I should put it clear

We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem.

There can be many reason for log file to grow out of proportion some are long running uncommitted transaction a transaction with begin tran but no commit this will hold the logs from being truncated when next log backup occurs. When you said too many VLF file it immediately strike to m that you have not set proper value for autogrowth. In your case it seems to me that you have kept value too low. I would ask you to read This link you can use below query to check auto growth and when it is happening.

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

My question is shrinking will only reduce from the end of the file to the last active portion of the log.

Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file. For example, if you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but the file cannot be reduced to sizes such as 433 MB or 525 MB. A virtual log file that holds any active log records, that is, an active virtual log file, is part of the logical log, and it cannot be removed

Therefore should I change to simple recovery first, then perform the shrink?Following this I will change back to full perform a full backup to start the backup chain again.

Changing recovery model to simple would deprive you from point in time recovery. Also you need to take log backup just before changing recovery model to simple and you have to take full backup after changing it again to full. Other thing to note is if you dont need poin in time recovery please let your database in simple recovery in this case you would not have to take log backups to truncate log.

If your database was in full recovery how often did you took transaction log backup please note that in full recovery only transaction log backup truncates log (if nothing is holding it)

What does below query return

select log_reuse_wait_desc from sys.databases where name='db_name'

above query will tell you what is holding the logs and not allowing them to truncate and hence reused.

Finally you must read Why does transaction log keeps growing out of space