Sql-server – When shrinking log files what difference does TRUNCATEONLY make

sql servertransaction-log

When using DBCC SHRINKFILE (Transact-SQL) to shrink log files does adding TRUNCATEONLY do anything different? if so what?

Scenario:
I have used DBCC LOGINFO and discovered I have too many VLFs, I only have a single .ldf I am not making any changes to the data files. My database is in FULL recovery and I can run t-log backups on demand. I plan to use Fix_VLFs.sql from the tiger team to rebuild my .ldf

When you look at the solutions generated by Fix_VLFs.sql it lists both options for shrinking the number VLFs

USE [MyDB];
DBCC SHRINKFILE (N'MyDB_log', 1, TRUNCATEONLY);

and 

DBCC SHRINKFILE (N'MyDB_log', 1);

The solutions are listed for use when the database is in FULL or SIMPLE recovery, respectably. But I can't see how FULL or SIMPLE would make any difference on if you should use TRUNCATEONLY or not. Nor Can I see how TRUNCATEONLY would make any difference for any log file shrink.

VLFs are wrap around files (Transaction Log Physical Architecture) as I understand it the original equipment VLFs('0" in CreateLSN) will not be deleted with SHRINKFILE. Additionally only those VLFs later in the LSN chain than the last one with a DBCC LOGINFO 'status' of '2' are available to be deleted.

In other words, the only option is to delete VLFs from the end of the wrap around, you need to work the current VLF (last status 2) around to the front (original equipment) of wrapped file, run the t-log backup and then you can use SHRINKFILE to delete everything except the original equipment VLFs. This would seem to be the only option without regard for the use of TRUNCATEONLY.

Best Answer

I see no difference when using TRUNCATEONLY when compared to regular SHRINKFILE on the log file. Here's the script I built for testing (2016 dev edition, note the DROP IF EXISTS syntax):

USE master
GO

DROP DATABASE IF EXISTS trunctest
CREATE DATABASE trunctest
 ON  PRIMARY 
( NAME = N'trunctest', FILENAME = N'C:\SQLTest\DATA\trunctest.mdf' , SIZE = 8MB , FILEGROWTH = 8MB )
 LOG ON 
( NAME = N'trunctest_log', FILENAME = N'C:\SQLTest\LOGS\trunctest_log.ldf' , SIZE = 8MB , FILEGROWTH = 8MB )
GO

ALTER DATABASE trunctest SET RECOVERY SIMPLE
--or use the below two lines if you want to test FULL recovery
--ALTER DATABASE trunctest SET RECOVERY FULL
--BACKUP DATABASE trunctest TO DISK = 'NUL'
GO

USE trunctest
GO

--Log files at creation
DBCC LOGINFO


--Let's make a table and a large transaction
CREATE TABLE fluff
(junk char(8000))

BEGIN TRAN
INSERT fluff
SELECT TOP 1000 REPLICATE('a',8000)
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2

--pre commit
--DBCC LOGINFO

--post commit
COMMIT
--DBCC LOGINFO

--CHECKPOINT to clear inactive VLFs
CHECKPOINT
--Or log backup if testing FULL
--BACKUP LOG trunctest TO DISK = 'NUL'

--DBCC LOGINFO

--Now let's shrink and check
DBCC SHRINKFILE(trunctest_log)
--Run this instead on round 2
--DBCC SHRINKFILE(trunctest_log, TRUNCATEONLY)
GO

SELECT size*8.0/1024 AS LogSizeInMB
FROM sys.database_files
WHERE type = 1

DBCC LOGINFO
Related Question