Tempdb Shrink takes long time.

Question

Tempdb Shrink takes long time.

Siva Ramasamy

SSCarpal Tunnel

Points: 4026
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

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

Thanks.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

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)

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

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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

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

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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)

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

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

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

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

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

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

Eduard SSCommitted Points: 1761 More actions · Answer 13

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.

Eduard SSCommitted Points: 1761 More actions · Answer 14

Eduard

SSCommitted

Points: 1761

January 13, 2015 at 3:45 pm

#1769969

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