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.

SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server shrinkfun

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 (https://blog.sqlauthority.com), Image source unknown.

,
Previous Post
SQLAuthority News – MSDN Subscription Giveaway Announced
Next Post
SQL SERVER – Computed Column and Performance – Part 3

Related Posts

45 Comments. Leave new

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

    Reply
  • 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.

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

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

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

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

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

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

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

    Reply
  • nice post for this post i learn some thing.thanks.

    Reply
  • vanessa toro
    May 3, 2013 6:17 am

    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

    Reply
  • Thanks for sharing your real Experience !!!!!!!!!
    Great.

    Reply
  • But whether can we shrink LOG files, otherwise it will grow and beyond the control. Please mention it.

    Reply
  • Thanks Pinal…

    Reply
  • Hi Pinal,
    would like to ask the right way to maintain your database if you have this kind of problem regarding space.

    1. Recovery Model – Simple
    2. Differential Back up everyday – retain at least 3 days latest back up
    3. Every week Full Back up – delete created differential back up
    4. Create Maintenance Plan for rebuild and reorganizing indexes

    in order to avoid database shrinking.

    Thanks a lot!

    Reply
    • Full backup is baseline for differential. You need to retain it till next full backup is complete.

      Reply
  • I have truncated all the tables in a database which is about 250GB in space. But after truncating I have found not a single MB of size is reclaimed. How to get space after truncating the table in a database?

    Reply
    • you need to give sometime so that ghost cleanup can catch up.

      Reply
      • kirubakaran (@amalkirpa)
        November 24, 2015 12:37 pm

        Can you explain in detail about ghost cleanup Process. i have face ghost cleanup in my envi regularly. so that my system got some performance issue. how to minimize or best practise for ghost clean up.

  • SELECT
    ‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
    + ‘DBCC SHRINKFILE (N”’ + mf.name + N”’ , 0, TRUNCATEONLY)’
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    FROM
    sys.master_files mf
    JOIN sys.databases d
    ON mf.database_id = d.database_id
    WHERE d.database_id > 4;

    Reply
  • Hi @Pinal,

    I’m using shrinkfile to remove data from the database log after anonymising a database backup – to ensure the log can’t simply be replayed should the backup become compromised.

    Would you agree this is a legitimate use case? Or is there a better way of achieving the same aim? I’d be interested in your viewpoint on this.

    Many thanks, and keep up the good work.

    Kind regards,
    Philip D.

    Reply
  • Hi pinal, great post .i already face this situation,
    but after shrinking the log files ,i executed maintainance plan(de-fragmentation).

    and its work fine .no issue has been came yet

    Reply
  • Hi Pinal,
    in my prod server TempDB Get Full i am going to shrinking is bad ? is it effect to Performance ?

    Pelase suggest

    Thanks

    Reply

Leave a Reply

Menu