Post reply

Tempdb Shrink takes long time.

  • January 10, 2015 at 3:48 pm

    #293631

    Dear SQL Experts,

    Thanks for looking into my question.

    I am trying to shrink a 170 GB tempdb file to 160 GB. There is more than 30 GB available in the drive.

    The shrink operation has been running for more than 30 minutes.

    Why would it take this long? Any idea?

    Thanks!

    Siva

  • January 10, 2015 at 8:58 pm

    #1769271

    Siva Ramasamy (1/10/2015)

    Dear SQL Experts,

    Thanks for looking into my question.

    I am trying to shrink a 170 GB tempdb file to 160 GB. There is more than 30 GB available in the drive.

    The shrink operation has been running for more than 30 minutes.

    Why would it take this long? Any idea?

    Thanks!

    Siva

    Yes. It's busy.

    The real questions are...

    1. Have you identified what made it grow so large?

    2. Have you fixed it?

    If the answer to those question results in a final "NO", then you shouldn't bother because it'll just grow again.

    For those getting ready to say to never shrink TempDB while it's online, please see the following:

    http://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/

    --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)
    Intro to Tally Tables and Functions

  • January 11, 2015 at 9:31 am

    #1769305

    Why in the world are you worried about 10GB on a 170GB file?!? Leave it and move on - I GUARANTEE you that you have MANY more things to pursue in your SQL Server environment/application than that issue!!! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 11, 2015 at 8:15 pm

    #1769377

    May be when you try to shrink tempdb, the between tempdb pages are not aloowing to free up space

    Thanks.

  • January 11, 2015 at 8:53 pm

    #1769378

    TheSQLGuru (1/11/2015)

    Why in the world are you worried about 10GB on a 170GB file?!? Leave it and move on - I GUARANTEE you that you have MANY more things to pursue in your SQL Server environment/application than that issue!!! 😎

    It might be just a test to see how long it would take.

    --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)
    Intro to Tally Tables and Functions

  • January 12, 2015 at 5:19 am

    #1769455

    Just because there is 30gig of free space doesn't mean that that 30 gig is at the end of the file. The data can be spread all over the file so when you shrink it it has to move the data pages around to free up the 10gig of space at the end of the file. That can take a long time.

  • January 12, 2015 at 6:02 am

    #1769463

    Markus (1/12/2015)

    Just because there is 30gig of free space doesn't mean that that 30 gig is at the end of the file. The data can be spread all over the file so when you shrink it it has to move the data pages around to free up the 10gig of space at the end of the file. That can take a long time.

    I believe the OP was stating that there was 30GB free on the drive, not within the tempdb file itself.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 12, 2015 at 8:04 am

    #1769517

    Dear experts,

    Thanks for showing interest in my question and sharing your knowledge to help me out.

    The total drive size is about 2.5 TB out of which the "tempdb" is occupying about 1.15 TB.

    The SQL instance is hosting data warehouse database (of size 200GB) . I think 1.15 TB is much big for "tempdb".

    so I am trying to understand whether it is acceptable to have a tempdb of size 1.15 TB or not. or is it some inefficient staging process that is causing this?

    Before I go talk to the data warehouse team, I want to make sure I have all the DBA points in hand to discuss with them.

    Regards

    Siva.

  • January 12, 2015 at 8:38 am

    #1769531

    Siva Ramasamy (1/12/2015)

    Dear experts,

    Thanks for showing interest in my question and sharing your knowledge to help me out.

    The total drive size is about 2.5 TB out of which the "tempdb" is occupying about 1.15 TB.

    The SQL instance is hosting data warehouse database (of size 200GB) . I think 1.15 TB is much big for "tempdb".

    so I am trying to understand whether it is acceptable to have a tempdb of size 1.15 TB or not. or is it some inefficient staging process that is causing this?

    Before I go talk to the data warehouse team, I want to make sure I have all the DBA points in hand to discuss with them.

    Regards

    Siva.

    That's not anything like your original post where you said TempDB is 170GB. Personally, I think that even that "small" number means that there' some pretty nasty queries that should be fixed but a 1.15TB TempDB mean that there's some code that's doing a massive many-to-many join that's probably not necessary. You need to find that code and fix it. Then, check on the initial size and growth of TempDB, fix it if necessary, and then bounce the SQL Service to reduce the size of TempDB. Trying to shrink something that large will take a month of Sundays to do.

    --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)
    Intro to Tally Tables and Functions

  • January 12, 2015 at 8:47 am

    #1769533

    Thanks Jeff...! Let me investigate further on this issue.

  • January 12, 2015 at 10:25 am

    #1769573

    Could also be long-running transactions, snapshot or RCSI isolation level use, many concurrent queries doing large hashs and/or sorts, suboptimal indexing, crappy queries, huge/bad cursors, etc.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 12, 2015 at 10:27 am

    #1769574

    Thanks Kevin for your inputs. I will consider them while I am analyzing this issue.

  • January 12, 2015 at 10:40 am

    #1769580

    TEMP tables are stored in TEMPDB as well. You won't know until you look at some code.

  • January 13, 2015 at 3:44 pm

    #1769968

    is it an option to shutdown sql server and start again ? sql rebuilds the tempdb when it starts again.

    there are some options to decide which size you want to start it with to prevent it from growing from 1gb in steps of 10% for example.

  • January 13, 2015 at 3:45 pm

    #1769969

    (besides the "where does it come from question")

Viewing 15 posts - 1 through 15 (of 25 total)

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