Here is one of the most popular questions I often come across- How do you move TempDB to a different drive?
Question – How to move the TempDB to different drive when the log files are filled?
Answer – In most of the cases which I have observed one has to move the TempDB to different drive when TempDB log file is filled up or one believes when moving to different drive will help the growth of the file. Sometimes user also moves to different drive due to performance reasons as keeping TempDB on a different drive from your main database helps.
Here is the error user usually engage when they come across TempDB log file growth.
The LOG FILE FOR DATABASE ‘tempdb’ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free up SOME LOG SPACE
Here is my earlier blog post where I have described how one can change the TempDB location to another drive SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.
What to Check Before and After You Move TempDB
Before running the ALTER DATABASE commands, look at the current files with SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb'). Newer installs often have several data files, and each one needs its own MODIFY FILE statement. Write down the old paths too, so you know exactly which files to clean up later.
The new folder must already exist, and the SQL Server service account needs full rights on it. If it does not, SQL Server cannot create tempdb at startup and the service will not start, which is a scary moment on a production server.
The change takes effect only after the service restarts, because tempdb is created fresh every time SQL Server starts. Plan the restart for a quiet time, since every connection drops. You do not copy the old files. Once the service is up and the query above shows the new paths, you can delete the old files.
One more tip: if the log keeps filling up, a new drive only buys time. Check for a long running open transaction with DBCC OPENTRAN('tempdb') first.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
