Resize the current allocated space for Tempdb

Question

Hi,

I want to reduce the current size of tempdb. It grew to 8gb. Initially set to 8MB - it grew to 8gb. As it stands most of the space in temp db is now free. Due to contranints I cannot restart sql server which would reset the temp db mdf file size. 

I tried couple of options as below :

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=<size of the data file>);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=<size of the log file>);
GO

I want to reduce the currently allocated space for tempdb without having to restart sql server services. Is there a possible way to achieve this ?

Please guide..

Thanks,

---

EVA05

Answer 1

Few links on the similar issue, have a look

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/7fd4c213-5314-4a0e-9c44-b93d97a390d2/

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0aa22872-4010-4009-b448-0c5f1107b0d7/

Regards
Satheesh

Answer 2

Hi,

The only way to do this "online" is to be certain that there is no activity occuring in tempdb and the only way to be sure of that is to be running in single user mode which kind of defeats the purpose.

If you attempt to shrink a tempdb files while tempdb is being used it may lead to corruption errors and would require a restart to re-build tempdb anyway.

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

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Try this it worked for me:

USE [tempdb] 
GO 
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) 
GO

Regards,Eshwar.

---

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

Answer 4

I have tried all of the above options. It does not reduce the space allocated to the tempdb mdf file. I want to reduce the mdf file size of the temp db which is currently 8gb. Although very less space is actually used now- it grew to 8gb because at some point it encountered the usage. I know if I restart services it will automatically reduce the mdf file size of tempdb. But as restarting services is not an option I am looking at other ways to achieve this.

Please guide..

Thanks

---

EVA05

Answer 5

Click on tempdb and set new size to 8 MB.

It just re sized of file

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

Check the tempdb.mdf .it will sized 8 MB.

It shows in SSMS 8Gb.

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 6

 can you tell exact step sequence that would allow to reduce the mdf file size for tempdb.  I could not locate the option to set new size when I click tempdb or right click tempdb

---

EVA05

Answer 7

A shrink will only work if there is nothing locking the data, the only way to ensure that is to stop processes using tempdb and the only way to be certain of that is to restart or single user mode. There is no magic behind this the shrink will either work cleanly or it won't and you will need to take the outage.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!