SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server

I was recently called to attend the Query Tuning Project. I had a very interesting experience in this event. I would like to share to you what actually happened.

Note: If you are just going to say that shrinking database is bad, I agree with you and that is the main point of this blog post. Please read the whole blog post first.

The problem definition of the consultation was to improve the performance of the database server. I usually fly to the client’s location a day before, so the next day I am all fresh upon reaching the client’s office after a relaxing night’s sleep. Due to a fair availability of the flight, I reached the location earlier that day, at around 2 PM. I headed to the client’s location to familiarize myself with the place. I met the resident DBA and we talked for a few minutes. We looked at the index fragmentation during our conversation. It was the heaviest fragmentation that I have ever seen in my life. I asked the DBA if he could rebuild or reorganize indexes. He replied that he has never done it in the past one year. One year is really a long time; I could not believe that they have no maintenance task scheduled. I even wondered how they managed without index maintenance for the entire year. Anyway, I quickly handed my index script over him.

We waited until the end of their business hours, at about 5PM. After this, there are hardly any activities in the database. We executed the task on their production server after checking a few settings. The script ran for about 1.5 hours until it is finished. Afterwards, I checked the fragmentation of the indexes; it was very descent. In fact, some of the large tables were showing the fragmentation near to zero. I was pleased with this while the DBA confirmed that few reports even ran faster than before. Satisfied with the work done on Day 0, I left the location and went to the hotel where I stayed.

When I arrived to the location the next day, I had a meeting with the company director. He mentioned that he was updated by Dr. DBA regarding my de-fragmentation of indexes, and he wanted to see the status of the indexes. I ran my script once again to check the fragmentation of index. To my surprise, it was just like yesterday. I got confused and speechless. I checked the server instances and a few other things, but still nothing. The Sr. DBA also had no explanation at all. He started to mumble something I did not understand. Well, to make the long story short, I had a long face and did not feel comfortable. I was pretty sure that I had run the script of de-fragmentation and it worked fine.

Before I continued, I tried to check all the existing stored procedures and jobs. Finally, just like magic, I found the following code.

Before running the following code. Read the whole blog post.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

This code shrinks the whole database on a single SQL Server Instance. I instantly figured out where this code was used, and then I removed it. After I got rid of the code, I rebuilt and reorganized indexes. For the next 5 days, I faced no problem at all. Well, this is another reason not to shrink the database. Shrinking the database causes heavy fragmentation of the tables and reduces the performance. After shrinking, it seems that rebuilding indexes is necessary. But again, there should not be any real need to shrink the database. Do NOT shrink your database.

Reference: Pinal Dave (http://blog.sqlauthority.com), Image source unknown.

43 thoughts on “SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server

  1. Nice post, thank you. Sometimes shrinking is necessary. I think you are correct with respect to the above piece of code, but you can prevent the fragmentation from happening during the ‘shrink’ if you use the below:

    dbcc shrinkdatabase(N’ns_shrink_demo’,1,truncateonly) with no_infomsgs

    The ‘truncateonly’ modifier does not perform any page movement, i.e. http://msdn.microsoft.com/en-us/library/ms190488.aspx

    This doesn’t shrink it to the extent that the ‘notruncate’ (default) does, but it still allows for much less space and no fragmentation problems…

    FYI, great blog, I read it everyday.

    mjfii

    Like

  2. As usual, great post. If there’s one thing that really bugs me are DBA’s (usually at the request of the business for more space) that set up shrinking. I worked in an environment where this standard operating procedure. But every week they would come to me asking why the database was performing terrible. It was always a constant battle.

    Like

  3. Hi,

    As per my knowledge the ‘DBCC SHRINKDATABASE’ is used to decrease the actual size of the database which is not used,does dbcc is helpfull in query tuning?

    Koteswar rao

    Like

  4. Thanks Pinal.

    I have become a follower of your posts.

    This is a wonderful and very informative Post.

    Generally its said that DB Shrink is Bad. But what other Options do we have to release the unused space in the Database. Its always not feasible to keep on adding extra Disk to your DB when there is already a lot of space available in the database.

    Like

  5. Hi Pinal,

    This is really wonderful post. I know many DBAs used to shrink database regularly which is not good. At the same time question comes, should we allow ever growing transaction log? of course NO.

    If you observe your database usage, you can plan out enough size for your transaction log and datafile so it won’t go nasty over your expectation.

    Apart from that, keep regular backup policy for full backup, transaction log backup and differential backup. that is all you need.

    In short I am against regular database shrinking.

    Like

  6. Just a quick aside to those of you who want to get rid of “unused” space in your database files. That “unused” space is usually there for a reason, so don’t assume that getting rid of it is a good idea. You need a certain amount of open space in the database for index maintenance, for example.

    Are there times to shrink files? Certainly, but not without a thorough understanding of why, and how.

    Thanks.
    -D.

    Like

  7. great article. making sure your DB is a large enough size in the beginning also largely eliminates file fragmentation of the data and transaction log files. Auto shrinking will also help file fragmentation every time sql needs to increase the DB file sizes, so auto-shrinking is a no-no.

    Like

  8. Also there is a good article from Paul Randall about this

    http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx

    So I just have one question about: the following code (using trunctateonly) causes fragmentation too? anyone realize a test after defrag thedatabase and run this?

    dbcc shrinkfile(‘database_namefilelog’, 512, truncateonly)

    In my experience, this doesn’t generate fragmentantion.

    Pinal, please your help to clarify this.
    Thanks.

    Like

  9. Hello!

    As always, a great, practical post. This clearly demonstrates why shrinking is bad – gaining that xGB of space might be very lucrative, but is clearly not worth the impact.

    Only scenarios where we use shrinking is when we have to ship the database to a different physical location on a disk/via the FTP or when we need to migrate to a new physical server. In both these cases, we would anyway run the re-index and usual scripts – therefore the negative impact of the shrinkage is compensated.

    Like

  10. Thanks Pinal and others for this discussion I want to know do we have any alternative to meet both the needs
    1)Eliminate unused space in DB (since sometimes this space becomes huge)
    2)Avoid Index Fragmentation (Rebuilding the indexes)

    What should be efficient step to achieve these goals?

    Regards,
    Nishit

    Like

  11. Hello, I am using SQL Server 2005. I am Working on big Project. So, I want this, when i creating table(s) ,i also do code documentation with that table. Simply, i want when create table place definition about every field. when any other developer generate the script of that tables. he will be to read the comments on that tables.
    Please help me, i heavily need it…..
    Thanks
    Sushil Shanky

    Like

  12. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts – August 2010 Journey to SQL Authority with Pinal Dave

  13. Pingback: SQL SERVER – What the Business Says Is Not What the Business Wants Journey to SQL Authority with Pinal Dave

  14. Shrinking a db that has grown to large for it’s disk may be necessary. defragging your indexes after shrinking is an option if you must shrink. Remember that it is very expensive for sql server to expand your data and log files when free space runs out. So if you shrink your data and your logs with dbcc shrinkdatabase command be sure to allocate a percentage of freespace to remain using the target-percent. How to determine the amount? Look at the growth of your log files over the past months and make a determination as to its future growth given what you find there. ou want to leave enough freeespace so it doesn’t expand for a year. You should do daily checks on your db so keep an eye on it’s growth after you have shrunk it. All in all shrinking should be done cautiously and only when absolutely needed. Never automatically or on a schedule!

    Like

  15. Pingback: SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance Journey to SQL Authority with Pinal Dave

    • Hello Pinal
      I m working on a window app and i m also using sql 2000.My client has a 10 year old data and which size is .mdf=26 gb and .Log=35 gb.The problem is that i want to reduce Database size without any affect plz help me

      Like

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

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

  18. Hi Pinal,

    In my production server i didn’t have much space and they are not ready to increase the space. for that i have deleted some data what they don’t want.
    after that i didn’t noticed any more space in the drive.

    But when i perform the database shriniking i got around 80gb free space. with out shirniking the database how can i get the space.

    Like

  19. Hi all,

    We have an application that access the sql db 24/7 – the dba’s are;
    checking the db integrity
    rebuilding indexes
    update statistics
    shrinking the database
    This is being done weekly at a time where the application is still very active on the db – can this cause any corruption issues?

    Like

  20. I see lots of people asking the same question that I have. “What do you do, if not shrink your databases, if you are out of hard drive space?”. Yet I really haven’t seen an answer from Pinal regarding this very question.
    What do we do if the databases are so very large the only thing left to do is shrink them?

    Like

  21. i have questions:
    1. how to find out the unused space in both database and log files prior to shrinking?
    2. if it is bad then why microsoft provided the auto and manual shrinking options?

    Like

    • Hi Bourne,
      run below script will give you free space detail for a database.

      select
      [FileSizeMB] =
      convert(numeric(10,2),round(a.size/128.,2)),
      [UsedSpaceMB] =
      convert(numeric(10,2),round(fileproperty( a.name,’SpaceUsed’)/128.,2)) ,
      [UnusedSpaceMB] =
      convert(numeric(10,2),round((a.size-fileproperty( a.name,’SpaceUsed’))/128.,2)) ,
      [DBFileName] = a.name

      from
      sysfiles a

      Like

      • Query doesn’t work. What’s a.name?

        Msg 4104, Level 16, State 1, Line 1
        The multi-part identifier “a.size” could not be bound.
        Msg 4104, Level 16, State 1, Line 1

        Like

  22. No answer as to how to reclaim space. I shrink after reindexing and have not found any issues at all. I don’t believe this post especially since there are no responses!

    Like

  23. I received the SCOM alert that the Ultrasound Log file is running out of space. Further checking that the Ultrasound Log file is over 40GB while the Ultrasound Database file is only 15MB. I noticed that the Ultrasound Database is set to “Full” recovery model.

    1. Should I perform a shrink file operation on the Ultrasound Log file ?
    2. Should I change the recovery model to “Simple” ?

    Like

  24. I’m sorry, but I’m new in shrink stuff could anyone explainme how exactly shrink process is? also which store procedures are related?, thanks

    Like

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

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