TempDB Shrinking

Question

Post reply

TempDB Shrinking

gupta1282

Default port

Points: 1465
More actions
March 9, 2010 at 7:28 am

#216322

Hi All
Our TempDB mdf file size is 32617 MB and Unallocated Space showing 32607 MB
but size of mdf file is not reducing after shrinking.
Why This happening ? How to regain this huges amount of space
Thanks
Ghanshyam

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

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

ramyours2003 SSChampion Points: 12268 More actions · Answer 1

ramyours2003

SSChampion

Points: 12268

March 9, 2010 at 7:35 am

#1130654

reboot the services , and observer the size

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If your tempdb has grown to this size, it did so for a reason. I would not even attempt to shrink it as it will just have to grow again when it needs addtional space. This will affect system performance, and could result in a very fragmented tempdb database file at the OS level.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Are you sure that you should shrink the tempdb? If the databases reached its current size during normal work, then there is no need to shrink it because it grow again. On the other hand if you did a one time action that caused the DB to grow to its current size or if something was changed (you moved a database to a different server, the work load on this server dropped because of organizational changes, etc’), then it is O.K to shrink it. The way to do that is to run dbcc shrinkfile command. You can read about it in BOL.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

ramyours2003 (3/9/2010)
reboot the services , and observer the size

I’m sorry but this is not really a valid reason to reboot server. Also it is not guaranteed that the TempDB will be smaller after the reboot. It depends on the initial size that it is configured to.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Big warning !! Discover this when someone setup a "shrink tempdb" job and errors would then occur.

If you run DBCC SHRINKDATABASE, no other activity can be occurring within the tempdb database such as sorting caused by "order by" in any SQL Statement"

If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may later receive multiple consistency errors, such as:

Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

Effectively, the only viable method for shrinking tempdb is to restart SQL Server.

SQL = Scarcely Qualifies as a Language

GTR SSCrazy Eights Points: 8007 More actions · Answer 6

As others said if TempDB mdf file has grown during normal business hours there is a reason why it has grown, there is no need to shrink because it will grow again. However you cannot shrink database below it's initial size no matter what.

If you still want to shrink database, Use Truncateonly option, as Notruntcate will only free data files and will not return unallocated space to OS.

EnjoY!

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 7

We have a lot of boot happy SQL users on this forum! 24/7 production servers could do without that...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I would setup some monitoring to watch the tempdb growth. You need to find what is causing the database to grow like that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqldba4u Hall of Fame Points: 3414 More actions · Answer 9

Run this before performing shrink operation.

Use tempdb

go

DBCC FREESYSTEMCACHE(ALL);

DBCC FREESESSIONCACHE;

then perform your shrink operation.

Thanks

Vivek

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Vivek29 (3/9/2010)
Run this before performing shrink operation.
Use tempdb
go
DBCC FREESYSTEMCACHE(ALL);
DBCC FREESESSIONCACHE;
then perform your shrink operation.
Thanks
Vivek

Why?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

CirquedeSQLeil (3/9/2010)
Vivek29 (3/9/2010)
Run this before performing shrink operation.
Use tempdb
go
DBCC FREESYSTEMCACHE(ALL);
DBCC FREESESSIONCACHE;
then perform your shrink operation.
Thanks
Vivek
Why?

Agree, why would you do that on a production server?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

CirquedeSQLeil (3/9/2010)
Vivek29 (3/9/2010)
Run this before performing shrink operation.
Use tempdb
go
DBCC FREESYSTEMCACHE(ALL);
DBCC FREESESSIONCACHE;
then perform your shrink operation.
Thanks
Vivek
Why?

Because shrinking TempDB is going to slow everything down, you might as well refresh the cache as well. 😛

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jack Corbett (3/9/2010)
CirquedeSQLeil (3/9/2010)
Vivek29 (3/9/2010)
Run this before performing shrink operation.
Use tempdb
go
DBCC FREESYSTEMCACHE(ALL);
DBCC FREESESSIONCACHE;
then perform your shrink operation.
Thanks
Vivek
Why?
Because shrinking TempDB is going to slow everything down, you might as well refresh the cache as well. 😛

If it ain't broke, break it real good :-D:-P

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqldba4u Hall of Fame Points: 3414 More actions · Answer 14

Who said to run it production server? I am giving an alternative to shrink tempdb without restarting server as asked in the very first post.This will help I guess:cool: