Shrinking Temp DB

Question

Post reply

Shrinking Temp DB

dwilliscp

SSCrazy Eights

Points: 9270
More actions
October 2, 2013 at 8:16 am

#299311

I am trying to shrink the first temp db mdf, but it is not shrinking... 95% free... just added some new mdf's.
Using this command:
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 17724)
I get the following:
DbIdFileIdCurrentSize MinimumSizeUsedPages EstimatedPages
214029552 10241016010160

Viewing 10 posts - 1 through 9 (of 9 total)

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

..Whats the current size of the tempdb in MB ..?

And how much MB are you trying to shrink it to ?...

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

If the tempdb is actively in use, you may not be able to shrink it until the objects stored there are cleared out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

dwilliscp (10/2/2013)
I am trying to shrink the first temp db mdf, but it is not shrinking

IN two cases , size will not get reduced.

1) Shrink will not work on active transational part.

2) size will not get reduced below initial size.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

Bhuvnesh (10/3/2013)
dwilliscp (10/2/2013)
I am trying to shrink the first temp db mdf, but it is not shrinking
IN two cases , size will not get reduced.
1) Shrink will not work on active transational part.
2) size will not get reduced below initial size.

Ah.. number 2 would explain it.. How do you get it to shrink below the size it was created with... Alter Database tempdb Modify File (name = temp1.mdf, size=100mb).. then wait for a reboot?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Yup.

Generally it's not a good idea to shrink tempDB anyway. Unless the system is completely quiesced, shrinking TempDB can cause corruption that will require a restart to fix (there's a kb article on it)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 6

Gail, I had to create extra MDF's to deal with SGAM contention, so now I need to shrink the old file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yes, I gathered as much. What I said stands.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dan-572483 SSCoach Points: 15870 More actions · Answer 8

"Have you tried turning it off and on again?"

If you're able to schedule a downtime, do it. Simpley stopping the SQL Service and restarting will return the TempDB to its initially configured size. Before doing that make sure the configured size is large enough for normal operations and has an appropriate number of equally sized files.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 9

dan-572483 (10/8/2013)
"Have you tried turning it off and on again?"
If you're able to schedule a downtime, do it. Simpley stopping the SQL Service and restarting will return the TempDB to its initially configured size. Before doing that make sure the configured size is large enough for normal operations and has an appropriate number of equally sized files.

Yes, it created the file at the old... large .. size. I will make the change once we get to the monthly maintenance day.