A while back I did a post about why you shouldn’t shrink your data file. This one is going to be similar in some ways, different in others.

TL;DR: It’s pretty pointless and will almost certainly cause performance issues.

Let’s start by asking why you might want to shrink your data file.

It’s too big

I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as big as it is. This also goes in with the I need to free up disk space with no good reason why the space needs to be freed up.

There are good reasons to shrink the data files and they do revolve around space. For example:

• We just archived/deleted a large amount of data that we don’t plan on replacing.
• We planned for a certain amount of growth and it just isn’t happening.
• We’ve redesigned the database and some of the data that used to reside in this file lives somewhere else now. (New filegroup, adding multiple data files etc.)

It helps performance

I’ve heard people say performance but that’s always been one of those magical “I had a problem and this fixed it” or “I’ve been told it helps” type things without any proof behind them. I honestly can’t think of any way a smaller data file is going to help performance. (It can help with restore time but let’s hope that’s unusual and it probably isn’t worth the cost.)

Billing

I get billed at the end of the month for the size of my database so I want it as small as possible before the billing cycle. Yep. I’ve heard that one. It’s even valid. They understand the reasons why they shouldn’t do the shrink and are trading temporary performance issues for $$$.

So let’s think this through a bit. What happens when we shrink a data file?

It get’s smaller! Duh!

Well yes. But unlike a log file where everything stays in the same order and extra space is pulled off the end things get moved around a bit in the data file.

--Set up DB & a couple of tables with data
CREATE DATABASE ShrinkMe;
GO
USE ShrinkMe;
GO
CREATE TABLE Table1 (GUIDId uniqueidentifier CONSTRAINT pk_Table1 PRIMARY KEY, 
Col1 char(50), Col2 char(50));
GO
CREATE TABLE Table2 (Id INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_Table2 PRIMARY KEY, 
Col1 char(50), Col2 char(50));
GO
INSERT INTO Table1 
SELECT TOP 1000 newid(), a.name, b.name
FROM master.sys.all_columns a
CROSS JOIN master.sys.all_columns b;
INSERT INTO Table2
SELECT TOP 1000 a.name, b.name
FROM master.sys.all_columns a
CROSS JOIN master.sys.all_columns b;
GO 20

-- Check fragmentation
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table1'),  
DEFAULT, DEFAULT, DEFAULT)
UNION ALL
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table2'),  
DEFAULT, DEFAULT, DEFAULT);
GO

There is definitely some fragmentation. We had best fix that.

ALTER INDEX pk_Table1 ON Table1 REBUILD;
ALTER INDEX pk_Table2 ON Table2 REBUILD;
GO
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table1'),  
DEFAULT, DEFAULT, DEFAULT)
UNION ALL
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table2'),  
DEFAULT, DEFAULT, DEFAULT);
GO

Much better! But of course rebuilding indexes increases the size of our database. Best shrink it!

DBCC SHRINKFILE(1,10);
GO
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table1'),  
DEFAULT, DEFAULT, DEFAULT)
UNION ALL
SELECT * FROM sys.dm_db_index_physical_stats (
db_id(), object_id('Table2'),  
DEFAULT, DEFAULT, DEFAULT);
GO

So you can see right off the biggest problem of shrinking a data file. All of your indexes suddenly become massively fragmented. So how did our above reasons check out?

Size: Because you are going to have to rebuild all of your indexes after a shrink you are probably going to increase your space right back again. Of course this may not be true if you have dramatically reduced the amount of data you are storing, but even then you need to decide if the extra space is worth the effort. Particularly since your DB will probably be continuing to grow over time anyway and it’s always a good idea to leave room for growth.

Performance: Fortunately in the case of data files we can use IFI(instant file initalization) so the file growths are pretty quick (although they do take time). However, you are still going to have to rebuild your indexes after the shrink, which will grow your DB back at least some, and increase your maintenance time. And to the best of my knowledge, there is no actual performance gain from a smaller data file.

Cost: You’re basically gaming the system here, but sure, if it works, and you are willing to accept the poor performance during the billing period (you don’t want to rebuild the indexes because you want to keep your DB as small as possible) then go for it.

Filed under: DBCC, Microsoft SQL Server, Performance, SQLServerPedia Syndication Tagged: best practices, data files, DBCC, microsoft sql server, Performance