Post reply

Tempdb Shrink takes long time.

  • January 14, 2015 at 10:26 am

    #1770173

    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.

    I don't think anyone else picked up on this, but you should consider separating your tempdb files to a different drive. When I set up production boxes, every file type in use will get its own LUN. Another point would be to use multiple tempdb files, and pre-size them. I work in a pretty big environment, so I will typically start with 4 tempdb data files sized to 200gb each, though I'm toying with the idea of setting the number of tempdb files to what I have MAXDOP set to. But I haven't tested that just yet. My thinking is based on reading that each thread in a process can use a different file, so if i have MAXDOP = 6, then I would use 6 tempdb data files. We shall see :w00t:

  • January 14, 2015 at 10:27 am

    #1770174

    Hi, I have restarted the SQL Instance before and it grew up again.

    Thanks!

    Siva.

  • January 14, 2015 at 10:28 am

    #1770175

    You have some process that is doing it... maybe large temp tables... or are their any rebuild index jobs with sort in temp db running>?

  • January 14, 2015 at 10:32 am

    #1770176

    What is the size set to here?

  • January 14, 2015 at 11:33 am

    #1770191

    I have not had my coffee yet but I thought that on recent versions of SQL server that TempDB was cleared each time the instance started. Might it take less time to bounce the service than to mess with this thing live? Also if there are live connections you are asking the server to shrink something while other folks are adding to it. Hmm.

    ATBCharles Kincaid

  • January 14, 2015 at 11:44 am

    #1770195

    Siva Ramasamy (1/14/2015)

    Hi, I have restarted the SQL Instance before and it grew up again.

    Thanks!

    Siva.

    Did you remember to decrease initial size of tempdb files?

  • January 15, 2015 at 9:45 am

    #1770503

    I have reduced the size of the files in the GUI "Database Properties" from 150+GB to 100 GB and restarted the server (Please look at the attachment). But that did not help. The size was same after the restart.

    Am I missing something?

  • January 15, 2015 at 3:36 pm

    #1770581

    The screen shot shows your initial size for the first file at 155GB, not 100GB.

    If this picture is incorrect I can suggest tinkering with the data management views to detect processes with multiple joins and what not (as Jeff and Kevin had indicated might be the cause). Try starting here : http://msdn.microsoft.com/en-us/library/ms177648(v=sql.110).aspx.

    ----------------------------------------------------

  • January 15, 2015 at 3:38 pm

    #1770582

    I tried to change the size to 100,000 here...but it won't accept..

  • January 15, 2015 at 5:53 pm

    #1770606

    You should also do explicit database log backups (as well as normal backups) or make sure your recovery model is set to "simple" or your log file will grow out of control.

    I have had hard disks fill up because of this.

  • January 15, 2015 at 7:34 pm

    #1770619

    phil.doensen (1/15/2015)

    You should also do explicit database log backups (as well as normal backups) or make sure your recovery model is set to "simple" or your log file will grow out of control.

    I have had hard disks fill up because of this.

    Just to quickly comment on the above - The temp database log is in simple recovery model, is not one that get backed up . Also the correct recovery model should not be dictated by the inability to properly plan the database landscape, including needed disk space of course.

    Back to the OP :

    Earlier a post pointed to http://support.microsoft.com/kb/307487/en-us . Did you have the opportunity to read through the article? It does state that

    "it is safe to run shrink in tempdb while tempdb activity is ongoing. However, you may encounter other errors such as blocking, deadlocks, and so on that can prevent shrink from completing. Therefore, in order to make sure that a shrink of tempdb will succeed, we recommend that you do this while the server is in single-user mode or when you have stopped all tempdb activity." Have you tried this?

    Plus "A simple restart of SQL Server resets the size of tempdb to its last configured size. " It looks like you had at 150+GB by default to begin with.

    ----------------------------------------------------

Viewing 11 posts - 16 through 25 (of 25 total)

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