SQL server 2005 creates few default databases to control and manage its services and other tasks. tempdb is used to store temporary information about the database and server usage and with the passage of time the size of this database started increasing and if you have a large database your default location that is mostly windows own drive got filled and you got no chance other than deleting the tempdb file and lose all the trace of your database and SQL server usage. There is a very simple solution available and that is to move the tempdb to another location where you have a large amount of disk space available for storage. For this you need to open a new query in your SQL server and issue the under given commands:
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘C:tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘C:tempdb.ldf’)Note: Please don’t copy and paste the commands
Output of above commands is given as under:
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
After issuing the above commands please restart the SQL server and you will see that tempdb.mdf and tempdb.ldf files have been created to new location.
