Tempdb Shrink takes long time.

Question

Tempdb Shrink takes long time.

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

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

sqldriver SSChampion Points: 10440 More actions · Answer 1

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:

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 2

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

Thanks!

Siva.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

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>?

sqldriver SSChampion Points: 10440 More actions · Answer 4

sqldriver

SSChampion

Points: 10440

January 14, 2015 at 10:32 am

#1770176

What is the size set to here?

Charles Kincaid SSChampion Points: 13593 More actions · Answer 5

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

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 6

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?

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 7

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?

MMartin1 One Orange Chip Points: 27799 More actions · Answer 8

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.

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

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 9

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

phil.doensen Mr or Mrs. 500 Points: 501 More actions · Answer 10

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.

MMartin1 One Orange Chip Points: 27799 More actions · Answer 11

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.

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