When Using DBCC SHRINKFILE for log files can we use TRUNCATEONLY OPTION
Background
This article is about DBCC SHRINKFILE command. Recently I found an anomaly in the definition provided for NOTRUNCATE option which can be used while executing Shrinkfile command. Please refer to this link http://msdn. microsoft. com/en-us//library/ms189493. aspx. If you go down to NOTRUNCATE OPTION it says that
'Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink. NOTRUNCATE is applicable only to data files. The log files are not affected'
**
** One marked as bold seems incorrect to me
The statement log file is not affected seems wrong or misleading to me. It does not move any pages for transaction log file but what is missing in definition is it cuts out inactive part of VLF( Virtual log file) , the VLF's which are marked as truncated or marked as reusable . In my opinion instead of mentioning that it is not applicable to log files this information should be added in the documentation.
To read more about Virtual Log files I suggest, please refer to this article
Performing the test
I did a little test to prove this on SQL server 2008 R2 Adventure works Database. You can download the AdventureWorks2008R2 database from this link.
use AdventureWorks2008R2
go
dbcc loginfo(AdventureWorks2008R2)
go
Below is the result I got
Total Virtual log files are 183
Now I will run DBCC SHRINKFILE command as per below code. I used 2 as it it log file ID you can also use Log file logical name
dbcc shrinkfile (2, TRUNCATEONLY)
go
Now to see how many free Virtual log files have been returned back(Cut from log file) to the system . I ran below command
use AdventureWorks2008R2
go
dbcc loginfo(AdventureWorks2008R2)
go
Summary
From above figure we can see that count of VLF decreased from 128 to 28 which implies that space used by all inactive VLF have been returned to the system. Also, please note that last row in the output , see the status value is 2, this also confirms that all rows after this were truncated VLF's.
Suggested Readings
http://msdn.microsoft.com/en-us//library/ms189493.aspx
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
http://technet.microsoft.com/en-us/library/aa933049(v=sql.80).aspx
Caution
If you schedule a job in your SQL server with command as below.
USE db_name
go
DBCC SHRINKFILE (N'db_name_log' , 0, TRUNCATEONLY)
GO
And you don't often take transaction log backup you might end with problem. If end of the virtual log file has status 2( it is holding an active transaction) .I mean to say if last virtual log file holds active transaction then you will get error like 'Cannot shrink log file because the logical log file located at the end of the file is in use.
So I would caution any body using this command in production that too on daily basis.As a fact I don't find any use of this command .Instead of using this you might use shrinkfile without truncate only.I am always in favour that a proper transaction log management will almost keep check on your log files.
See Also
SQL Server General & Database Engine Resources on the TechNet Wiki
List of articles by Shanky
Other Languages
This article participated in Technet Guru Competition January 2014 and won Bronze Medal