1

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.

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.

  • Thanks this is the answer to the question I was originally asking. – Tom Sep 30 '14 at 12:30
  • @RFL you should first ask user to check what is holding log before suggesting to take multiple log backup consider scenario where log backup is huge and actually long running transaction is holding the log – Shanky Sep 30 '14 at 18:47
1

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

  • Thanks for the detailed answer. On the point of shrinking does a shrink remove inactive VLFs regardless of where they are in the log? – Tom Sep 30 '14 at 12:28
  • @Tom - VLFs are only removed from the end of the log file. That is why the cycle of CHECKPOINT, BACKUP, DBCC SHRINKFILE works. Once the highest VLF is cleared the log rolls back to the lowest VLF (simplified view) and then the higher VLFs can be discarded by the SHRINKFILE. – RLF Sep 30 '14 at 12:46
  • When u shrink any free space must come from end of file it removes inactive log file beyond last active virtual log file if target is not specified .When specified its rounded off and space is freed from end of file keeping target in picture read article about shrinking i gave its mentioned there – Shanky Sep 30 '14 at 14:25

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.