Shrink Job in SQL Server

Posted on 2011-10-21
Last Modified: 2012-05-12
I am running a shrink job on SQL Server 2005 and the database called TempDB became massively huge as in over 200 gigabytes.  I just started a shrink job on the database, so how long will this take before this finishes?
Question by:VBBRett
    LVL 8

    Accepted Solution

    It might not shrink as expected.

    You might be known of below, still a reminder -
    Shrinking tempdb is not similar as other databases.
    It can not be shrunk if any activity is going in tempdb. This is reason why you are suggested to start SQL Server in single user mode to perform this activity.

    If the question is about time consumed in shrinking any database -
    The answer is - this is variable, the size depends on the way how extents are organized in your database. So, in other words, this is not possible to predict time consumed.
    LVL 25

    Assisted Solution

    Its always recommended to shrink database files rather than shrinking database as a whole. Again while shrinking database files, you can do it in batches, i.e. shrink it in 100 MB to 5 GB batches depending upon your requirement. You would like to learn more about dbcc shrinkfile

    Apart from that your doubt regarding tempdb is genuine. tempdb is the place where all the user activites happen. It is a master database. When you are shrinking your database, the activities involve tempdb so its growing huge. So, shrinking database files in batches is recommendable.
    Again to know about which database is using what files, (the logical names), you can make use of any of the system objects:-

    You can create your own logic to perform the task. Let us know in case there is any other thing we can help.
    LVL 26

    Expert Comment

    A database will shrink only if the space alocated to it is not used. If TempDB grew so big is because it HAD to. Some activity required sorting, grouping  or huge temporary tables, ehich cause this.

    An easy way to shrink tempdb is to restart the SQL server but this doesn't guarantee that it won't groe again later. You have to find out what causes it and at the same time make sure to give enough room to tempdb files to grow.

    Here is a good article:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now