Shrinking data files - Things you shouldn’t do with SQL

This is the first in a follow up from my SQLBits and DDD sessions.

Why is shrinking files bad?

Shrinking files is bad because

1. Your file will have grown for a reason

2. If its a regular occurrence SQL will just have to grow the file(s) again

3. Growing a file is a blocking activity (except for data files when instant file initialisation is turned on)

4. It causes fragmentation

Whatever you do, don’t stick shrink file into your code as a standard practice. Understand why the file is growing and do something about it.

Blocking

One of the biggest issues with regular shrinking is that the files have to grow again. Growing of data files can be quick with instant file initialisation , but even, then if your data files share the same drive as other files then you can get file fragmentation.

For all growth operations they block operations in the database and so your applications will stop.

Growing of files

If your files are growing then understand why. Common causes are

1. updating more data than you need to.

This often happens when you have a cartesian join in your UPDATE which the update hides, this can result in the same row being updated > 1. Also the INSERT, UPDATE design pattern I will be talking about later will also cause this when you populate a table with a value that you then update with a larger (in bytes) value.

2. not getting minimally logged operations when you need them

The biggest cause of this is using full recovery mode. Other causes are, you’ve added an index or the table isn’t empty thus resulting in fully logged operations. Look up trace flag 610 if using SQL 2008.

3. You are updating too much in one transaction

Consider batching your updates/deletes/inserts so you don’t have to have space in the transaction log for ALL the updates. Also note that the overhead of the transaction log means that even if you insert 10GB your transaction log might need much more, say 20GB. So don’t be surprised if this happens.

Fragmentation

Lets look at the fragmentation, create a blank database

use master

go

alter database shrink set single_user with rollback immediate

go

drop database shrink

go

create database shrink

go

use Shrink

Deploy the handy SP to find the pages allocated to an index/table

Put some data in the database so that the shrink has something to shrink

create table SpaceFiller (ID int identity(1,1), Padding char(8000))

go

insert into SpaceFiller

select top 256 ''

from msdb.sys.columns

Now create a table that is going to get fragmented

--Put some data in the file

create table OrderedTable (ID int  primary key, Padding char(8000))

go

--Add our ordered table and insert rows into it

insert into OrderedTable

select top 3200 row_number() over(order by object_id) id, ''

from msdb.sys.columns

order by id

Lets reindex the table this is a common scenario that causes free space in a file after which people shrink the file.

alter index all on OrderedTable rebuild

exec up_ShowIndexPageOrder 'OrderedTable'

This shows you that the pages are contiguous

image

Now lets shrink the file

 

--Shrink the file to free the space

DBCC SHRINKFILE (N'shrink' , 0)

GO

--Look at the page order now

exec up_ShowIndexPageOrder 'OrderedTable'

And what do we get

image

This highlights that the pages are now effectively reversed where a page points to the previous page in the file and not the next page.

You can issue a reorganise and the pages will be correctly sorted. However this is more IO and changes that will get logged and thus cause problems with

Summary

If you are getting odd growth in your data files/transaction log, if you can’t afford the space then you need to find out the root cause of the growth.

Don’t shrink your files as a regular practice.

Size your files as big as they need to be so they don’t have to grow except in an emergency

Published Saturday, October 23, 2010 10:35 PM by simonsabin

Comments

Saturday, October 23, 2010 11:47 PM by SimonS Blog on SQL Server Stuff

# Shrinking data files - Things you shouldn’t do with SQL

This is the first in a follow up from my SQLBits and DDD sessions. Why is shrinking files bad? Shrinking