Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Tempdb datafile size

Author  Topic 

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2013-06-14 : 18:15:55
Hi,

Tempdb data file size shows 50,200GB. In database properties, it shows free space 50,000GB. I want to reduce tempdb datafile size. I tried this option:

In database properties, changed initial size to 2000, shrink database, no effect.

How to decrease tempdb data file size without restarting sql server service?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-14 : 22:05:37
DBCC SHRINKFILE()
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-06-18 : 15:25:42
A post on shrinking TempDB with restart.http://www.sqlserver-dba.com/2011/01/shrink-tempdb-without-sql-server-restart.html

But, if it all possible - a sql server restart is the way to go

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-26 : 00:37:46
quote:
Originally posted by jackv

A post on shrinking TempDB with restart.http://www.sqlserver-dba.com/2011/01/shrink-tempdb-without-sql-server-restart.html

But, if it all possible - a sql server restart is the way to go

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



I disagree. Shrinking TempDB is painless and doesn't require a service restart.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-06-26 : 10:47:44
It's definately an interesting one. The link I posted refers to methods of shrinking the TEMPDB without a restart , but a Microsoft knowledgebase has some thoughtful comments about errors appearing when TEMPDB is being used .
The article doesn't explicitly link shrinking with corruption - but these comments are worth considering
http://support.microsoft.com/kb/307487

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-07-02 : 07:52:48
Hi Peter,

With ref to your questions, there are thousands of discussions/debates on tempdb; however, the ultimate solutions is to go with restart the service or go with shrink, rest of the discussion you will get to know what are the factors affecting the tempdb to grow.

Thanks,
Sri.
Go to Top of Page
   

- Advertisement -