Shrinking a Database with TRUNCATEONLY
Published: Jun 24, 2016
I received a comment the other day on my previous post about never shrinking a database in which I was asked whether TRUNCATEONLY was safe to use as Books Online state that no data pages are moved.

Aside from the fact that I had initially misunderstood the question (my bad), I did happily concede that yes, it is a safe option to use HOWEVER, it does have its quirks and therefore I thought I would give it its own post.

The problem is actually presented as part of the Books Online description because the reason it is safe and moves no data pages is because it simply removes any free extents from the end of the data file. The fun part being that in production you likely have no real idea whether the space you want to reclaim is at the start, middle, or end of the data file.

Here’s an example to show how this option works, and to highlight the flaw.

Firstly let’s create 2 test databases:

use master
go

-- change the file path as required

create database test
on
(
      
name = 'test', fileName = N'E:\SQLData\test.mdf', size = 5000KB, maxSize = unlimited, fileGrowth = 1024KB
)
log on
(
      
name = 'testLog', fileName = N'E:\SQLLogs\test_log.ldf', size = 512KB, maxSize = unlimited, fileGrowth = 1024KB
)
go

create database test2
on
(
      
name = 'test2', fileName = N'E:\SQLData\test2.mdf', size = 5000KB, maxSize = unlimited, fileGrowth = 1024KB
)
log on
(
      
name = 'test2Log', fileName = N'E:\SQLLogs\test2_log.ldf', size = 512KB, maxSize = unlimited, fileGrowth = 1024KB
)
go


What I’ve done here is to create 2 small databases we can play with. Let’s have a look at their initial sizes:

select name, size
from sys.master_files
where database_id in (db_id('test'), db_id('test2'))
order by database_id
go


So now we’ll create a single, but large, table in each database and fill them with 2500 pages.

We will also have a clustered index on the table to ensure that the pages are created in order in the data file. This is vital to proving the TRUNCATEONLY concept.

use test
go

create table testTable
(
      
id int identity,
      
misc char(7000) default('a'),
      
constraint pk_testTable primary key clustered(id)
)
go

insert into testTable default values
go 2500

use test2
go

create table testTable
(
      
id int identity,
      
misc char(7000) default('a'),
      
constraint pk_testTable primary key clustered(id)
)
go

insert into testTable default values
go 2500


Now let’s have a look at the database sizes:

select name, size
from sys.master_files
where database_id in (db_id('test'), db_id('test2'))
order by database_id
go


Now we’re going to delete some of our data and therefore leave us with free space to reclaim.

In the first database we’re going to remove all data pages at the end of the data file, but in the second database we’re going to remove the same number of data pages but at the beginning and middle of the data file leaving some at the end (this will also generate some log records, which will be useful for the demo as well):

use test
go

delete from testTable
where id > 100
go

use test2
go

delete from testTable
where id < 2400
go


Let’s have another look at the file sizes. We should see that the data file sizes are the same (showing SQL never gives space back - hence the shrink problem as a whole) but the log files have grown.

select name, size
from sys.master_files
where database_id in (db_id('test'), db_id('test2'))
order by database_id
go


So now let’s run our SHRINK commands with TRUNCATEONLY and see what happens:

use test
go

dbcc shrinkfile('test', truncateonly)
go
dbcc shrinkfile('testLog', truncateonly)
go

use test2
go

dbcc shrinkfile('test2', truncateonly)
go
dbcc shrinkfile('test2Log', truncateonly)
go

select name, size
from sys.master_files
where database_id in (db_id('test'), db_id('test2'))
order by database_id
go


As you can see… only our data file with empty pages at the end was able to shrink. For the other database TRUNCATEONLY is utterly useless to us.

If you consider your live environment you will never be able to guarantee that the data you have deleted is all at the end of the data file and, to be honest, I would imagine it quite unlikely and therefore you could find that although TRUNCATEONLY is safe, and therefore definitely worth trying, you may find yourself disappointed with the amount of space it actually reclaims. But definitely do try it first as if the results are satisfactory then it’s a lot easier and faster than the alternatives.

Quick cleanup:

use master
go

drop database test
go

drop database test2
go


As a final note, there seems to be confusion with the way Books Online describes this feature, implying it is a data file only operation but, as you can see from the above (which is why I wanted my log file to grow) it does indeed work on log files as well.

Hope this helps.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron