Reducing Initial TempDB Size

If the initial TempDB size is set to a higher value than what you want to set then this might not let  you do. This might cause problems on SQL Server 2005 and earlier versions. It would throw the following error:

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

However this issue has been fixed in later versions as per update from Microsoft:

http://connect.microsoft.com/SQLServer/feedback/details/285172/ssms-cannot-modify-tempdb-initial-size-appropriately

Workaround in SQL Server 2005:

1.) Stop SQL Server
2.) Start SQL Server in Minimal Configuration Mode:

sqlservr -f -sDATAC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe -f -sInstanceName

3.) Connect to the server from SQLCMD utility as only single connection can be made to the server.

sqlcmd -s InstanceName

4.) Execute the alter TempDB statements on SQLCMD utility

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 1024 MB)

5.) Restart SQL Server in Normal Mode.