Sql-server – DBCC SHRINKDATABASE TRUNCATEONLY – 2008R2 vs 2012

sql serversql-server-2008-r2sql-server-2012

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

2008 R2

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 statement 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 statement now tells me it only affects the log file?

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

Best Answer

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 what happens using TRUNCATEONLY with SHRINKDATABASE, here is a run down of what happened to a database called performance that I have installed locally.

SELECT @@VERSION;
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
   Jun 28 2012 08:36:30
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

USE performance;

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.

Here is some sample output from LOGINFO. 

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
------ -------- ----------- ------ ------ ------ -----------------
2      253952   8192        23     2      64     0
2      253952   262144      24     2      64     0
2      270336   516096      25     2      64     24000000013400005

I then ran DBCC SQLPERF(LOGSPACE) and got the following output

DBCC SQLPERF(LOGSPACE)
/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance          9870,867             3,395626  0
*/

I then ran 

DBCC SHRINKDATABASE(PERFORMANCE, truncateonly)

and got the following result

/*
DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
8       1       66464       288         44944       44944
8       2       116584      72          116584      72
*/

Next, I reran

DBCC SQLPERF(LOGSPACE)

and got 

/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance     910,8047        37,08699            0
*/

SHRINKDATABASE with TRUNCATEONLY gave back over 9 GB of available space from the transaction log file back to the OS.

I tried the same experiment with another database called performance2

sp_spaceused

/*
Performance2 52.19 MB   22.81 MB
*/

DBCC SHRINKDATABASE(Performance2, truncateonly)

sp_spaceused

/*
database_name   database_size   unallocated space
Performance2 31.13 MB   21.13 MB
*/

Which gave back 20 MB to the OS. Using SQL Server 2008, SHRINKDATABASE TRUNCATEONLY shrinks both data and transaction log files.

Related Question