Thursday, January 24, 2013 4:33 AM
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 ?
Thursday, January 24, 2013 4:58 AM
Few links on the similar issue, have a look
Thursday, January 24, 2013 5:01 AM
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.
Thursday, January 24, 2013 5:04 AM
Try this it worked for me:
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) GORegards,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.
Thursday, January 24, 2013 5:17 AM
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.
Thursday, January 24, 2013 6:56 AM
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.
Thursday, January 24, 2013 2:37 PMcan 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
Thursday, January 24, 2013 8:36 PMA 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.