If you ever find your TEmpDB to be full and if you want to move TempDB, you will find this blog post very helpful.
Here is the error message which may come across.
Event ID: 17052
Description: The LOG FILE FOR DATABASE ‘tempdb‘ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
Up SOME LOG SPACE
Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.
Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to another drive.
1) TempDB grows bigger and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on a different physical drive helps to improve database disk read, as they can be read simultaneously.
Follow direction below exactly to move the database and log from one drive (c:) to another drive (d:) and (e:).
Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
USE TempDB GO EXEC sp_helpfile GO
Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:\Program Files\Microsoft SQL ServerMSSQL\datatempdb.mdf PRIMARY 16000 KB
templog 2 C:\Program Files\Microsoft SQL ServerMSSQL\datatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and templog by default. These names will be used in the next statement. Run following code, to move mdf and ldf files.
USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:\datatemplog.ldf') GO
The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.
Reference : Pinal Dave (https://blog.sqlauthority.com) ,Microsoft Support, Database Journal.
78 Comments. Leave new
Hi Guys..,
There is no way to reduce the tempdb size without restarting…? Please reply
it helped me too.
can i after that delete the original tempdb files from c:\ ?
Hello Pinal,
How does one move more than one TEMPDB mdf file to another location?
Can you provide the script?
Did you try alter database command?
Simple solution. Thanks!
Try to shrink the tempDb. If Shrinking the TempDB doesnt work try to run the the command dbcc freeproccache over the tempdb and then shrink the DB.
Hi Pinal,
I have a situation where I need to move the tempdb however this Instance is in a cluster 1 of 3 nodes. I need to move the tempdb so the Snap Manager for SQL Server can properly snap shot the DBs.
Will performing your commands above, affect the cluster/instance??
Hi Pinal,
Can I move tempdb to external usb harddisk ? I cannot extend server disk because its 2003 R2 ..
Thanks
Asrar
Hi Pinal,
I have SQL Server 2008 R2 with SP1 running in a clstered envrionment. When I moved the temp database file and restart the SQL server, it errors out. Here what event log say:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘O:\MSSQL10_50.MSSQLSERVERCMSS\MSSQL\Data’.
I used the same account to move the file that is used by the sql services.
It seems it is happening only in the clustered environment. Am missing on something here while moving the file in the clustered configuration?
Thanks,
Tarun.
Can we use the shrik tempdb while executing sql query in another session.
dbcc shrinkfile(templog, 0)
Hey guys, just my piece of advise. Look for the root cause of the problem rather than concentrating on the solutions. If you fail to identify the root cause of it, it will be a regular coffee for you every morning. Look and carefully examine your functions, views and stored procs. Test run each of them with analyzer to know how much does it take to query the data needed. If it returns on a longer time than expected, revise the codes to simplify. The better DBA you are if you can do it. Lastly, look for the queries on the application side. Many programmers don’t consider the load of the db server when executing queries. In my case, I found the bulk of the problem on the application side, especially on the transaction queries. I think tempdb is the one storing the snapshot of the tables before committing the commands so that on event of roll back, it can be restored. Hence, it holds lot of space depending on the size of the table and the data to be processed. Hope it helps you.
You are a joke! The path in your notes do NOT reflect a real install. One needs to specify a full path. “D:TEMPDB??” give me a break. It crashed my server.
Thank you for your article.
Hi Joe,
Please check all your path before executing any script.
Thanks….
I Think,If you add a new file to tempdb it wont effect untill unless the restart of the service.In this situation it canot be possible to avoid log space full error when the existing files reach to maximum size.
Thanks a lot.
I Faced This Question From My Senior Colic, If Your TempDB is full also u can’t able to log in into Microsoft SQL Server Management Studio, How you Clear the temp DB. that is production server you can’t restart SQL Server and make Tempdb as a default state ! Any one can answer to me , that would helpful to me
Hi,
Nice conversation!!!
But, how to find “which transaction is consuming the temp.mdf file” ?
I moved my master,model and msdb DB files from C to D Drive because of Low space on C, after restart SQL Server, it shows D Drive on all three DB’s, now i want to delete my old C Drive three old DB’s but i cant, when i stop SQL Server and delete that files from C Drive, i cant Start SQL Server again it shows error msg. Please advise me?
You can follow steps given on books online. https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017
Hi Pinal,
How to move back tempdb to its default master device?
What do you mean by “default master device” ?
It’s interesting that after quoting this post in your today’s post (02/23/2016), I had a chance to go through it. It is incorrectly spelled as ‘demplog’ instead of ‘templog’ in your post. Please check it.
Thank you, I fixed it!