SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Tempdb datafile size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter99
Constraint Violating Yak Guru

481 Posts

Posted - 06/14/2013 :  18:15:55  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 06/14/2013 :  22:05:37  Show Profile  Visit russell's Homepage  Reply with Quote
DBCC SHRINKFILE()
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 06/18/2013 :  15:25:42  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
5071 Posts

Posted - 06/26/2013 :  00:37:46  Show Profile  Visit russell's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 06/26/2013 :  10:47:44  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 07/02/2013 :  07:52:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000