Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Im a bit confused was the TRUNCATEONLY parameter changed in 2012 or is the documentation wrong in 2008R2?

2008R2

Releases all free space at the end of the file to the operating system
but does not perform any page movement inside the file. The data file
is shrunk only to the last allocated extent. target_percent is ignored
if specified with TRUNCATEONLY.

TRUNCATEONLY is applicable only to data files. The log files are not
affected.

The last statment makes me thing this has no effect on log files at all?

2012

Releases all free space at the end of the file to the operating system
but does not perform any page movement inside the file. The data file
is shrunk only to the last allocated extent. target_percent is ignored
if specified with TRUNCATEONLY.

TRUNCATEONLY affects the log file. To truncate only the data file, use
DBCC SHRINKFILE.

The last statment now tells me it only affects the log file?

So was the functionality changed or is there a error in the documentation or is my interpretation wrong?

2 Answers
2

TRUNCATEONLY affects both the LOG and the DATA files in 2008. On BOL for SQL Server 2012 the message simply indicates that if you only wish to SHRINK the database file, then you should use DBCC SHRINKFILE which will allow you to shrink either the data or log files.

For 2008, it is clearly indicated that TRUNCATEONLY only affects DATA files.

Lets test. To visualize the what happens using TRUNCATEONLY with SHRINKDATABASE. Here is a run down of what happened to a database called performance that I have installed locally.

I ran DBCC LOGINFO just to take a peak inside the transaction log and found that all of the virtual log files after about 200 were inactive, status = 0. In my performance database, all those inactive VLFs can be truncated and the space can be given back to the OS.

I just tested with a scratch database, and using the TRUNCATEONLY option caused both the data file and log file to be shrunk.

I believe what they're trying to clarify is that the TRUNCATEONLY option changes the behavior when shrinking the data file (i.e. don't rearrange any data pages, just chop off the unused space at the end), but the log file will still be shrunk normally.

If my understanding of the option is correct, then this is probably a better way to describe it:

The log file will still be shrunk normally when using the TRUNCATEONLY option. This option only affects the behavior when shrinking data files. To truncate only the data file, use DBCC SHRINKFILE.