Restart Server to shrink TempDB?

Question

Restart Server to shrink TempDB?

Draelith

Ten Centuries

Points: 1140
More actions
May 14, 2013 at 9:13 pm

#274008

So I had a bad query blow out the tempdb (dev, not log) database to 36GB. It's normally a lot smaller. I'm looking at the best way to resolve the issue and get it back to it's normal 1-2GB size.
Restarting the server seems to be one of the most recommended solutions, but I'm not sure if I understand correctly that it automatically fixes the size? If I go to the db files, it says the Initial Size (MB) is 35380. So if I restart SQL, won't it just go back to being 35380 if that's what the initial size is?
Do I need to do something beforehand like this?
use tempdb
go
dbcc shrinkfile (tempdev, '1000')
go
Then after I run that restart? Or do I not bother and simply restart?
Also, if I do have to run that code, do I have to stop the sql service and put it into single user mode?
It's not something I have to do very often, so I'm a little unsure of the best way forward here. Any help is greatly appreciated.

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

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

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

Draelith (5/14/2013)
So I had a bad query blow out the tempdb (dev, not log) database to 36GB. It's normally a lot smaller. I'm looking at the best way to resolve the issue and get it back to it's normal 1-2GB size.
Restarting the server seems to be one of the most recommended solutions, but I'm not sure if I understand correctly that it automatically fixes the size? If I go to the db files, it says the Initial Size (MB) is 35380. So if I restart SQL, won't it just go back to being 35380 if that's what the initial size is?
Do I need to do something beforehand like this?
use tempdb
go
dbcc shrinkfile (tempdev, '1000')
go
Then after I run that restart? Or do I not bother and simply restart?
Also, if I do have to run that code, do I have to stop the sql service and put it into single user mode?
It's not something I have to do very often, so I'm a little unsure of the best way forward here. Any help is greatly appreciated.

TempDB is one of those databases that you don't want to shrink with SHRINKFILE unless you're in the single user mode. I've not had it happen to me personally in the early days of my career but many have and they have had some corruption occur because of it.

Just bounce the service and call it a day. Either that or leave it where it's at until you need to bounce it for a different reason unless you just absolutely can't afford the space it's using.

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

karthik babu Hall of Fame Points: 3335 More actions · Answer 2

I have the same thought as you do. But if the tempdb is too full, find the queries or SPs responsible for the tempdb growth and stop them before shrinking it. Otherwise it will never shrink.

I feel restarting sql server for this cause is not a good practice. Experts please advice.

Thanks.

KB

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Draelith Ten Centuries Points: 1140 More actions · Answer 3

So I don't need to use shrink file?

So if I'm right in my understanding, I just restart the sql server service and the temp file will reduce from 35GB back to... no idea. Shouldn't i have to specify the size before i restart?

karthik babu Hall of Fame Points: 3335 More actions · Answer 4

please avoid restarting the SQL Server. Use the below query to shrink tempdb after stopping the queries responsible for its growth.

use tempdb

go

DBCC SHRINKFILE(templog, 80)

GO

BACKUP LOG tempdb WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE(templog, 80)

GO

80 is mentioned in MB, you can have it in your choice, but wisely. This will not work in SQL 2008 or greater versions.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Draelith Ten Centuries Points: 1140 More actions · Answer 5

Well I'm using SQL Sever 2008 R2, so if that won't work, what will work?

karthik babu Hall of Fame Points: 3335 More actions · Answer 6

you can just use DBCC shrinkfile or DBCC shrinkdatabase command without truncate_only.

visit this site for details.

http://support.microsoft.com/kb/307487

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

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

DO NOT shrink TempDB with DBCC ShrinkFile or DBCC ShrinkDatabase unless the instance (not the database) is running in single user mode, it is documented to be able to cause corruption to TempDB.

Also, the suggested backup log for TempDB will fail.

The suggested code

BACKUP LOG tempdb WITH TRUNCATE_ONLY

fails with

Msg 155, Level 15, State 1, Line 1

'TRUNCATE_ONLY' is not a recognized BACKUP option.

and any other form of BACKUP LOG run against TempDB will fail with

Msg 3147, Level 16, State 3, Line 1

Backup and restore operations are not allowed on database tempdb.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

The safest way to 'shrink' TempDB is, as Jeff suggested, by restarting SQL. TempDB will go back to whatever size you defined it as (not what it grew to, what it was set to with ALTER DATABASE)

Please note that there's no real urgency to doing so, a larger than usual TempDB will not cause problems, so you can just restart SQL next time you have a maintenance window.

Make sure you've addressed the root cause first, the queries that caused the growth, or you'll just be back in the same situation in the future.

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

Draelith Ten Centuries Points: 1140 More actions · Answer 8

Thanks. That was the solution I was leaning towards. I'm not a fan of shrinkfile either.

Ok, so that leaves one final part of the puzzle unresolved though. The fact that if I go to properties on the tempdb, it comes up as 35,380 as the initial size for tempdev. If I restart, will it stay at 35,380, or do I have to first use the alter database command to change it back to 1,000 like so:

ALTER DATABASE tempdb MODIFY FILE

(NAME = 'tempdev', SIZE = 1000)

Then, restart? Also, to do the above, alter db, do i have to put the instance in single user mode?

I'm sorry if I'm missing it, but I just want to be 100% certain here as I really don't want corruption.

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

Don't think you can use alter database to set a lower size than current. Unless someone went and explicitly grew the database ALTER DATABASE TempDB ... to set the files to the 35 GB, a restart should set them back to their initial size (the size they were last set to with ALTER DATABASE)

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

Draelith Ten Centuries Points: 1140 More actions · Answer 10

Using this link, http://support.microsoft.com/kb/307487, method 1 looks like it used the alter db to shrink the tempdb.

But I'll give the restart the go first. Will schedule it with the sys admin. If it doesn't fix the issue, I'll have to look at something else. Thank you for the help everyone.

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

Draelith (5/15/2013)
Using this link, http://support.microsoft.com/kb/307487, method 1 looks like it used the alter db to shrink the tempdb.

Look closer. That has SQL started in minimal config, which means TempDB goes back to the installation time defaults and then ALTER DATABASE is used to *grow* TempDB from those install-time defaults.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 12

tempdb is a special database when it comes to redefning the initial size. Unlike other databases you can actually ALTER the files to be smaller than their current size without complaint from the engine and when the service is restarted the files will be initialized at the size you specified.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Draelith Ten Centuries Points: 1140 More actions · Answer 13

Ok, well the tempdb grew again to 37GB overnight from 35GB. As it was killing our space, we needed to restart the server. I stopped the SQL service and then we restarted the entire server. 5 minutes later, back up and running and the tempdb was still 37GB.

I then went into management studio, tempdb properties and went to the file section and manually changed it from 37000 to 1000. It ran the change and immediately on both SQL and the file system, the file had reduced in size to the correct 1GB file size.

So.... how come the file didn't auto shrink on restart? Could I have just done the second part without restarting the server?

Now I also have to find out why the tempdb is blowing out so much.... this could be interesting.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 14

If you go through Object Explorer use the Script Button to see what would be run if you clicked OK. To be safe I would recommend just running the ALTER DATABASE...MODIFY FILE statement yourself in a Query window.

As mentioned, if you do not find the root cause changing the initial size won't help you much. Find the root cause. Determine if it is something that can be avoided and remediate if possible. Then consider tempdb initial file size reduction.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato