On my current project, we’ve been running out of disk space on our development servers. The culprit is SQL Server’s tempdb. During installation, it was left on the c: drive, which is of course our smallest disk. User databases can easily be moved via detach/attach, but this doesn’t work for the tempdb. To move the tempdb, another solution exists. First, use


use tempdb
go
sp_helpfile
go

to see where the tempdb files are located at the moment (you’ll need this path in a moment). Move them to another place with


use master
go
Alter database tempdb modify file (name = tempdev, filename = 'd:\databases\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'e:\database_logs\templog.ldf')
go

and restart SQL Server. However, I noticed that even after the restart, the c: disk was still being used for the tempdb! The solution turned out to be to delete the tempdb.mdf and templog.ldf files on the c: disk (see the paths returned by sp_helpfile before the move).

Advertisements