Post reply

TempDB won't shrink

  • December 27, 2019 at 4:52 pm

    Go to Answer

    #3710795

    I have this tempdb issue which I can't seem to resolve. Current temp file size is almost 350GB. I know what caused it but that is no longer an issue. However, I am unable to shrink the file down to 100 GB but can't. I don't have large queries running, using tempdb. I used dbcc freeproccache as per Brent Ozar's article but no luck. Temp DB just won't shrink.

    https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/

    Any help is highly appreciated.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • December 28, 2019 at 5:10 pm

    #3710942

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 28, 2019 at 9:09 pm

    #3710964

    What is the exact command that you're using to do the shrink?

    How many files is TempDB made up of?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 31, 2019 at 1:59 pm

    #3711427

    I have tries using couple of different methods.

    1. DBCC SHRINKFILE
    2. DBCC FREEPROCCACHE
    3. There are 8 files in total.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • December 31, 2019 at 10:42 pm

    #3711558

    DBCC SHRINKFILE on its own isn't the full command... especially if you have 8 files in total.  I'm thinking that you need 8 separate commands for that along with a target size.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 2, 2020 at 12:25 pm

    #3711742

    I mean I just didn't run just DBCC shrinkfile. This is the complete command I ran for all 8 files but still no luck.

    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempdev' , 10240)
    GO

    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempdev2' , 10240)
    GO
    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempde3' , 10240)
    GO

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • January 2, 2020 at 1:20 pm

    Answer

    #3711747

    Just restarted the SQL Server service and it helped.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • January 2, 2020 at 7:11 pm

    #3711945

    LearningDBA wrote:

    I mean I just didn't run just DBCC shrinkfile. This is the complete command I ran for all 8 files but still no luck.

    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempdev' , 10240)
    GO

    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempdev2' , 10240)
    GO
    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempde3' , 10240)
    GO

    I wonder if the changes they made to automatically balance the size of the files (like Trace Flag 1117 used to be use for) is preventing the shrinks or if it's just that your TempDB database is always too busy.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply