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.

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

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 high.

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

You can notice that after rebuilding, Fragmentation reduces to a very low value (almost same to 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 rebuild 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 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 indexing is not the best suggestion as that will create database grow again.

I have always remembered the excellent post from Paul Randal regarding Shrinking the database is bad. I suggest every one to read that for accuracy and interesting conversation.

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

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 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 future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future. Brent has written a good summary blog post as well.

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

44 thoughts on “SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance

  1. Pinal,

    Good to see this article discouraging the use of Shrink Database. Most of the time when database files get too large it is the transaction logs that have grown, rather than the main database. In these cases the backup regime is not set-up properly or has failed. Some free space in the main database files is a good thing, because it makes future inserts faster.

    I have only ever used shrink database very rarely on a production database. However, if very large amounts of data have been removed from a database, then I think it maybe appropriate.

    Thank you for your excellent blog

  2. A good detailed explanation on why shrinking is bad. The question i have is since rebuilding indexes increases the database size, what would be a soultion to handle the issue, also REORGANIZE is not recommended either. Could you write a post about this topic: Possible solutions for decreasing fragmentation and not increasing the database size.

  3. @pin

    You have a solution in the link Pinal linked to http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

    [quote]

    So what if you *do* need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?

    The method I like to recommend is as follows:

    * Create a new filegroup
    * Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING) ON syntax, to move the tables and remove fragmentation from them at the same time
    * Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
    [/quote]

  4. Dear Sir,

    Your posted on Shrink Database is Bad is very awesome.

    However, I noticed some redundancy on your database scripts after the words of:

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

    Please help and advise.

    Many thanks for your kind help and sharing.

    Thanks,
    Edwin

  5. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  6. The sql script for “Now let us TRUNCATE the table and check the size and Fragmentation.” is missing the above post.

    However thanks a lot for the articles.

    Regards,
    Parikshit

  7. Hi pinal, i tried to shrink the database , but still the size is not reducing. do i have to change any properties for that. before shrinking and after shrinking the size is same .

  8. Pingback: SQL SERVER – Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31 « SQL Server Journey with SQL Authority

  9. I had a 5GB database, about 10 tables, one table had about 45 million rows and took about 2GB on disk. Each table has 1 CLUSTERED index with max 2 key fields, most only 1 key.

    If you REBUILD that, it would increase the database size on disk (not actual data) to over 7GB. Fragmentation would be next to 0%. If you SHRINK the file/database, it would reduce the size back down, but would increase fragmentation to about 98%.

    If you REORGANIZE, it would not change the size.and fragmentation would go down to 0.01%.

    It’s hard sometimes to get clients with 300GB databases to just double or triple the disk space, but it’s what should happen. (there should always be plenty of space to grow).

    What I need to create now is a test to illustrate to the client how their app performs at 98% fragmentation and how it performs at 0% fragmentation. That would be the ‘money query’.

    OH YEAH!!

    IF YOU DROP THE CLUSTERED INDEXES, you will get your HEAP back. Heaps do not shrink well. If I tried to shrink my table with heaps, instead of clustered indexes, it would shrink very little each time. Once I re-added a Clustered index on the large table, the free space was returned and I was able to then shrink and then reorganize to get back in the game.

    Thanks for this post.

  10. Hi. I read your stuff any chance I get. Thanks for all your help in the past. I am trying to deal with a database that got too big because I neglected to routinely clean out tables of data that builds up rapidly and doesn’t need to be kept. Now I want to shrink it. Like emma above, I am getting 0 for my fragmentation after running your scripts, rather than the big number. Is it possible that a patch fixed this issue since you wrote this article? If not, what do you advise I do? I want to gain a better score on my ‘first byte time’ for my website, and one of the suggestions is to make my database more efficient including cleaning out unused data and (I assume) to make it smaller, thus more efficient. If I shouldn’t shrink it, what are my options.

  11. Running the sys.dm_db_index_physical_stats query after truncate the table.
    I got the result like this avg_fragmentation_in_percent fragment_count
    0 0
    what might cause the problem?I use the sql server 2008 R2

    Thanks in advance

  12. Running the sys.dm_db_index_physical_stats query after truncate the table.
    I got the result like this avg_fragmentation_in_percent fragment_count
    0 0
    what might cause the problem?I use the sql server 2008 R2

    Thanks in advance

  13. I have the same problem as Diyig:
    running the sys.dm_db_index_physical_stats query after truncate the table.
    I got the result like this avg_fragmentation_in_percent fragment_count
    0 0
    why?

  14. 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!

    • 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?

  15. 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

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

  17. 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 ?

  18. 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.

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

  20. 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.

  21. 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?

  22. 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!

  23. 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.

  24. Hi pinal,

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

    Thank you.
    Harshad.

  25. 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?

  26. 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….. :-)

  27. 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.

  28. Pingback: SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s