The tempdb on my SQL server is located on the same drive as my main production database, E:\MSSQL\TEMPDB. This would cause problems with my SnapManager configuration.
Solution – moving the tempdb:
To relocate the tempdb you need to find a drive with enough space, and not the system drive as you don’t want your server falling over because you ran out of space. For me it was easy enough, my production database files are stored on the E drive and the production log files are stores on the F. I found enough space on the D: drive to relocate the tempdb data and log file. So I create a new TEMPDB folder on D – c
Open up a new query window within SQL and paste the following code:
USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TEMPDB\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\TEMPDB\templog.ldf'); GO
Once you have executed the above code you will need to restart the SQLServer service.
Once SQL has restarted, you will notice that there are still files in the original location, you can safely delete these now.