I would like to lower the initial size of tempdb.

I have tried running

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE
   (NAME = tempdev,
   SIZE = 2048MB)
GO

but get error

MODIFY FILE failed. Specified size is less than current size.

 thanks
 

Try this script from books online

--Increase the size of the tempdb device
DISK INIT name = 'tempdb1',physname = 'c:\mssql\data\tempdb1.DAT',vdevno = 100, size = 12800
GO
--Increase the size of tempdb
ALTER DATABASE tempdb ON tempdb1 = 25

Regards,

Sat

For expanding the dafualt logical file tempdev ua can also use the below

use master 

ALTER DATABASE tempdb ON tempdev = 25

The above with increase it by 25 MB

Regards

Sat

I guess if the specifies size is less than current size u cannot use this command , as your error ,message states.

I am not sure if restart and rebuild of system database can fix that, I hope gurus have something up there sleeve on this.

Regars

Sat

This worked for me , 

dbcc shrinkfile ('tempdev',6,TRUNCATEONLY)

Traget size in MB = 6

TRUNCATEONLY = gives the reclaimed space to OS.

I would like to ask what is the reason to shrink the TEMPDB size, if the other processes needs space for calculations then TEMPDB is used extensively and it will grow again. So try to reduce number of round trips in this case and I'm sure we can suggest a workaround if you can explain the problem.

the tempdb was just created with the wrong size. It was created on our reporting database with the same size as the production DB. The rep DB does not need a tempdb with the same allocated size as production. The reason I need it smaller is that the Initial size is 17gb and is taking up space on disk that is becoming limited.

Ok, follow as suggested above and you might still get in to the issues in going upper size, you have to control usage of temp database within the queries or run in smaller batches.

 I am not sure my problem is being understood correctly. I do not have a problem with the tempdb growing too large. It is almost never used. When sql server was installed on this server the settings for the tempdb were copied from our production environment (which does need a large tempdb). Since the tempdb was created at a large size I cannot decrease the size of the database beyond the specified Initial size. The size of the tempdb is 17gb which is unneccesarily large.

 Thanks for the replies. I have found a solution to my problem. I started sql server from command line using

sqlservr -c -f 

sql server started in  minimum configuration mode with a tempdb size of 1MB. I then ran  my statement above effectively increasing tempdb size from 1MB to 2048MB (but decreasing from 17GB). after restarting service tempdb size was 2048MB.

This problem is there for not only tempdb but any database, you cannot shrink it beyond the initial size, but I have tried a work around that has worked for me in the past.

Try re-setting the initial size of the data file by increasing it by 5 or 10MB and then try to issue the shrink command, it has worked for me

Satya 

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

 I have already found a solution, which I stated in my previous post. It is the same thing and the KB provided. thanks.

Ok, it will be hard without any such reference for the previous posts.