Tag Archives | tempdb

Move that SQL Server TempDB

Always a good source of SQL Server problems is to have your tempdb on the same drives as your production databases, or even worse, your system drive.

You should always repoint it during the install, or first thing after finishing the install as it requires a restart. And restarts are bad because:

  • Downtime == bad as far as businesses go
  • With SQL Server 2008, R2 and 2012 a restart loses lots of lovely DMV data that comes in really handy for diagnosing interesting problems.

So assuming you still need to shift it, it’s pretty simple.

USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\Logs\templog.ldf'); GO And then restart your SQL instance.

The above assumes noone’s sneakily renamed your tempdb files. If the have you’ll need to look up the names via SQL Server Management studio or use: use master; go SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); go