SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance

Earlier, I had written two articles related to Shrinking Database. I wrote about why Shrinking Database is not good.

I received many comments on Why Database Shrinking is bad. Today we will go over a very interesting example that I have created for the same. Here are the quick steps of the example.

  • Create a test database
  • Create two tables and populate with data
  • Check the size of both the tables
    • Size of database is very low
  • Check the Fragmentation of one table
    • Fragmentation will be very low
  • Truncate another table
  • Check the size of the table
  • Check the fragmentation of the one table
    • Fragmentation will be very low
  • SHRINK Database
  • Check the size of the table
  • Check the fragmentation of the one table
    • Fragmentation will be very HIGH
  • REBUILD index on one table
  • Check the size of the table
    • Size of database is very HIGH
  • Check the fragmentation of the one table
    • Fragmentation will be very low

Here is the script for the same.

USE MASTER
GO
CREATE DATABASE ShrinkIsBed
GO
USE ShrinkIsBed
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Create FirstTable
CREATE TABLE FirstTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_FirstTable_ID] ON FirstTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Create SecondTable
CREATE TABLE SecondTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_SecondTable_ID] ON SecondTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert One Hundred Thousand Records
INSERT INTO FirstTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Insert One Hundred Thousand Records
INSERT INTO SecondTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

Let us check the table size and fragmentation.

SQL SERVER - Shrinking Database is Bad - Increases Fragmentation - Reduces Performance ShrinkFrag1

Now let us TRUNCATE the table and check the size and Fragmentation.

-- TRUNCATE and SHRINKDB
TRUNCATE TABLE SecondTable
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

SQL SERVER - Shrinking Database is Bad - Increases Fragmentation - Reduces Performance ShrinkFrag2

You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database. If you notice the fragmentation, it is considerably higher.

The major problem with the Shrink operation is that it increases fragmentation of the database to very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive.

One of the ways to reduce the fragmentation is to rebuild index on the database. Let us rebuild the index and observe fragmentation and database size.

-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REBUILD
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

SQL SERVER - Shrinking Database is Bad - Increases Fragmentation - Reduces Performance ShrinkFrag3

You can notice that after rebuilding, Fragmentation is reduced to a very low value (almost same to the original value); however the database size increases way higher than the original. Before rebuilding, the size of the database was 5 MB, and after rebuilding, it is around 20 MB. Regular rebuilding the index is rebuilt in the same user database where the index is placed. This usually increases the size of the database.

Look at irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to the size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually.

Rebuild indexes is not the best suggestion as that will create database grow again.

Let us run following script where we Shrink the database and REORGANIZE.

-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

SQL SERVER - Shrinking Database is Bad - Increases Fragmentation - Reduces Performance ShrinkFrag4

You can see that REORGANIZE does not increase the size of the database or remove the fragmentation.

Again, I no way suggest that REORGANIZE is the solution over here. This is purely observation using the demo. Read the blog post of Paul Randal.

Following script will clean up the database

-- Clean up
USE MASTER
GO
ALTER DATABASE ShrinkIsBed
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE ShrinkIsBed
GO

There are few valid cases of the Shrinking database as well, but that is not covered in this blog post. We will cover that area some other time in the future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future.

Are you Shrinking your database? Well, when are you going to stop Shrinking it?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Quick Look at SQL Server Configuration for Performance Indications
Next Post
SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor

Related Posts

54 Comments. Leave new

  • Question, my DB grows by over 300% the same day I shrink it. I know that shrinking is bad but i pay for SQL space so I need to keep it as low as possible. How can I do that? You tell us shrinking is bad but don’t offer any solution as to how to keep the DB tight and efficient. Thanks!

    Reply
    • Yes we also have to pay the data center per kilobytel of file size. Our issue is that the we do not have sys admin rights on the server and the dba doing the backups is not ensuring that the transactions in the log files have been committed before running the nightly Full DB backups. Therefore the log files never shrink. we had a 200 mb databae with a 13 GB log file on the Test server!! HSince we have no need for a up to the minute recovery we have no issue with doing a database backup each eveningm,and keep the databses in simple recovery mode. But the Data center insist on maintaining every database in full recovery mode. But then we never use any log backups and as far as I can tell, none are being taken. So we end up with unnecessarily large log files. So how do we get the size of our database log files properly reduced without incurring the issues you referred above?

      Reply
      • Added in order to check notify me of follow up comments boxes Please delete upon moderation.

  • So basically it comes to which costs more… Bad performance or wasted disk space.

    Reply
  • Thank your Pina Dave, this article continues to be the basis for my thoughts on Shrinking and Index Fragmentation, thanks for the great work.

    Allan

    Reply
  • Hi, if someone in the organization has already shrunk a database, how do we get our performance back?

    Reply
  • We have had a MSSQL database for data warehouse of size 1.5TB and recently with the purging and arching we were able to cleanup some data and now we have 900GB for free space in data files. We have two different data file sitting in different LUNs which are made from same disk array of EMC storage.

    Is it still a bad idea when we have 900G of free space ?

    Reply
  • I have quick question. In oracle I use these commands to get my disk space back after deleting several rows from a table.

    SQL> alter table JBPM_BYTEBLOCK enable row movement;
    SQL> alter table JBPM_BYTEBLOCK shrink space;

    What will be the equivalent in MSSQL ? Does MSSQL automatically will shrink it ? In that case I wont needs any command.

    I dont think these 2 command is equvalent either.
    DBCC SHRINKFILE
    DBCC SHRINKDATABASE

    Please comment thanks.

    Reply
  • Ok thanks for the answer so the conclusion is shrinking is bad if I want to regain my disk space than I have to rebuild indexes right ? One more question if shrinking is bad in SQL database is that the same case in oracle database.

    Reply
  • I don’t think that shrinking is always that bad. I’ve got several dozens of relatively small databases (low-to-medium traffic websites), and, being aware of the performance problems after shrinking, I regularly shrink AND rebuild indexes. The reason I shrink is that those databases have some tables filling up with log entries that I also regularly delete, so there is unused space in them. I haven’t noticed significant size increases with shring+rebuild compared to the initial size. For example, I had a database reaching 4GB that was reduced to less than 1GB after shrinking and rebuilding indexes, since the other 3GBs were log entries that were deleted at some point. What’s your opinion on this specific scenario?

    Reply
  • best way to shrink datebase and rebuild index without any grow or fragmentation is backup base , drop base , restore from backup

    Reply
  • Shrinking the database to a minimal size will reduce the amount of free space which will lead to fragmentation because all pages have to be filled and data is scattered over the pages. After rebuilding the indexes, the database will be optimally defragmented, but the size will be increased significantly for the same reason. Isn’t it true that on one end you have the optimal defragmented database with a lot of free space, on the other end the optimally filled database that has to much fragmentation? Using disk space efficiently means you will have to find the optimum between these two. This means you will have to know why you are shrinking a database, and to what extent you need to shrink as well as what level of fragmentation is acceptable. For small databases this is not such a problem, but for big databases this is a true art!

    Reply
  • Jagdeep Mankotia
    March 22, 2013 5:11 pm

    Nice Activity example explained to perform.

    Reply
  • Jagdeep Mankotia
    March 25, 2013 5:25 pm

    We can use the below command to identify that, how much percentage of shrink process has been done.

    select percent_complete from sys.dm_exec_requests where command = ‘DbccFilesComp’
    SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time FROM
    sys.dm_exec_requests WHERE command = ‘DbccFilesCompact’

    Use * for more columns in it.

    Reply
  • Hi pinal,

    Can we shrink the log fie…??? will it affect the database performance..??
    Need your reply. Please

    Thank you.
    Harshad.

    Reply
  • i am getting after i loadedd 2 lack data in my sql -server 2008 data base … memory exception…please reply

    Reply
  • From this article and the comments, it seems that the ideal solution is to completely rebuild a new application database from an old database, or perhaps even better, to completely rebuild the tables first (in a new database), then add the indexes, etc, in order to achieve maximum defragmentation and space reclaim.

    Is there a decent software tool that can completely do this? Redgate?

    Reply
  • Hi Pinal,
    thanks for this article. Especially the step by step examples are very usefull to understand the working of these functions in SQL server. Things are more clear now….. :-)

    Reply
  • Andreas Jansson
    March 18, 2014 5:10 pm

    Hi Pinal,

    You wrote: “There are few valid cases of the Shrinking database as well, but that is not covered in this blog post. We will cover that area some other time in future. ”

    I’ve recently dropped an enormous log table from a database, and since the database is not meant to be that big again, within a foreseeable future, the obvious thing to do would be to shrink it.

    Please write an article about shrinking databases in a correct and controlled manner. There are enough of the ranting “do not shrink”, that some people devote themselves to.

    Reply
  • Nilesh Nagpure
    August 26, 2014 11:04 am

    Thanks Pinal

    Reply
  • Pinal, what version of SQL Server did you perform this test in? I am running it in 2012 SP1 and am not getting the size increase at the end.

    Reply
  • Pinal,
    I executed the script on 2010 SP1.
    Shrink operation which is made after the execution of truncate command, reduced the database size and made the fragmentation to “zero”.

    I got the following results..

    name Size_KB
    ShrinkIsBed 6144
    ShrinkIsBed_log 528

    avg_fragmentation_in_percent fragment_count
    0 0

    We are facing sudden increase in a database size currently. I need to find a solution for that.
    Can you please help me out.

    Thanks in advance.

    Reply

Leave a Reply