Tempdb shrink- sql server 2005\2008

Posted on 2011-10-31
Last Modified: 2012-06-27

Please let me know, how to shrink tempdb in business hours using tsql (sql server 2005 and 2008).

In business hours, we are receiving tickets related to tempdb volume that is there is no enough space in tempdb volume.

i have tried with tsql shrink command, but sometimes it won't releases free space in tempdb volume\disk.

Question by:sg05121983
    LVL 5

    Accepted Solution

    Try this script

    DECLARE @DB_Name nvarchar(255);
    DECLARE @DB_LogFileName nvarchar(255);
    SET @DB_Name = '<Database Name>';               --Input Variable
    SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
    'USE ['+@DB_Name+']; '+
    'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
    'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
    LVL 3

    Expert Comment

    you should not shrink the tempdb files.
    Here is why:

    But you need to ensure that you have enough space for tempdb, and that the T-SQL is proper and that you have the right indexes and table format.
    LVL 75

    Expert Comment

    by:Anthony Perkins

    The TRUNCATE_ONLY is a deprecated switch that is finally (thank goodness) no longer supported in SQL Server 2008 and SQL Server 2008 R2
    LVL 25

    Expert Comment

    If its not shrinking, it means there is something running that is consuming space in tempdb. Try checking if there are any open transaction during the time frame.

    Use tempdb

    dbcc opentran

    If there is some open trasaction and the starttime for that transaction is long back, check what is running in that spid. You can do it by running sp_who2 <the spid returned by sp_who2>.
    Try checking why the statement is consuming so much of tempdb or why it is running for such a long time. If everything is fine, check for the size you allocated for tempdb. You may want to increase the size of the file assigned.
    For temporary fix, you can kill the spid opened and then shrink the tempdb. But its always better to kill the problem.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    And here is the MSDN documentation on how to shrink tempdb:
    How to shrink the tempdb database in SQL Server
    LVL 50

    Expert Comment

    why do you want to shrink tempdb ... surely it requires more space is the jist of the message you've received?

    what other problems are you experiencing?
    LVL 68

    Expert Comment

    It's extremely difficult to shrink tempdb while SQL is running.  It's very easy to crash some running code that way.  IF  you can prevent all other activity to tempdb -- lol, yeah right -- then you can safely shrink it.

    However, you are almost certainly hurting your overall system performance by shrinking tempdb.  You should preallocate enough space to tempdb that it doesn't need to grow.

    Temporarily, if you're short of disk space for a period of time, you might consider moving a relatively lightly used db -- NOT tempdb -- to a slower, and thus cheaper, type of drive to free up fast disk space for tempdb.  Tempdb is too usually too critical for performance to relegate it to lesser drives.

    Author Closing Comment


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now