Midnight at 1 AM, I received a call from my customer where I have earlier worked on performance tuning project. As soon as I picked up the phone call the first statement from my friend was about TempDB and it was as follows:
“We should have followed your advice, the TempDB is full, now help us fix it.”
The matter of the fact, I exactly knew what was going on. A few days ago when I was there doing a review of their database health, I had noticed that they had a temp database on the local C Drive and it was getting filled up very quickly. I had warned them about this issue, however, I was told that they follow the principal of not fixing anything which is not broken. Honestly, I totally get that, but there are few special cases when we have to be proactive rather than reactive and keep our database in order.
Let us see a very popular interview question related to the moving temp database to new drive.
Question: How to move the TempDB to new drive when the drive is full?
Answer: Let us first see the error which is usually visible when the drive where TempDB is filled up.
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
There are major two reasons why TempDB needs to move from one drive to another drive
- Temp files grow bigger and the existing drive does not have enough space to accommodate them
- Moving temp database to another file group which is on a different physical drive helps to improve database disk read
Now here is a two step procedure to move the TempDB.
Step 1: Get Logical File Name
Run following script to get the logical name for the temp data and log file.
USE TempDB GO EXEC sp_helpfile GO
If you have not done any customization most of the time, you will see the logical file name as tempdev for mdf data file and templog for log files.
Now use the logical name in the following query and you will be able to move the temp database to the new drive.
Step 2: Move TempDB to New Drive
Now execute following scripts which will move your temp database to new drive.
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
When you execute above script you will see the message that query has run successfully. However, there will be no changes in how the temp database is configured. The location of the TempDB changed when SQL Server will restart services again. You will be able to see the changes only after the services are restarted.
Well, that’s it! We successfully changed the location of the Temp database to new drive.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
If the TempDB is filled up, restarting the Server will help it to free the space. But, in case of production servers, where we can’t afford to restart servers, we have to move tempDb to a diiferent physical drive in order to free the space. Please correct me if I’m wrong.
Then we copy the logical file in new drive, it’s need or not
You’ll still need to restart the SQL Server service for the new location of the TempDB to be activated. So either way there’ll be a downtime.
Hence as a long term solution, just move the TempDB files to a larger disk
You can’t just move the tempdb files, as suggested in the comments. You can however, move them, as documented in the article. A restart is required. However, if that is not possible, you can, just like any database, add another file, in tempdb, placing it on another drive. A restart will not then be necessary, but tempdev and templog will still be on the old drive. If you also do the alter, as described above, then the next time the server is restarted, then tempdb will be moved. Of course the new file that you added will also still be there.
Even if you move tempDB files to a different drive, you will need to restart sql services.
If it is a prod server and your tempDB is full, you could always identify the transaction which is consuming most space in tempdb and kill it or you may add another tempDB file at a different location. That way you would not need a restart.
add new file group in temp db database and select default file group option then next time all logs written in new file group in different drive. when you have down time then you can restart the SQL sever.
You should have tested the answer before writing comment…
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILEGROUP [TempDB2]
GO
Error:
Msg 1826, Level 16, State 1, Line 3
User-defined filegroups are not allowed on “tempdb”.
I need some help deleting temp DB which is default created in SQL2016, 8 of them. I need to delete temp5, 6, 7 and 8 so I will just have 4 of them. I use this query but not successful:
use tempdb
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( ‘ALL’)
GO
DBCC SHRINKDATABASE (tempdb,8)
GO
— Step1: First empty the data file
USE tempdb
GO
DBCC SHRINKFILE (temp8, EMPTYFILE); — to empty “tempdev12” data file
GO
–Step2: Remove that extra data file from the database
ALTER DATABASE tempdb
REMOVE FILE temp8; –to delete “tempdev12” data file
GO
Result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 3 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 4 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 5 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 7 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 8 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 9 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHRINKFILE: Page 9:87 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 21
Cannot move all contents of file “temp8” to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 24
The file ‘temp8’ cannot be removed because it is not empty.
As always, very helpful. Thanks man!
I made a change of parades from tempdb last night, after consulting this article, on a production server, which supports more than 800 people working. There was a problem, resulting from security flaws and + COM objects in the windows registry, which prevented all files from being created. To my surprise, when restarting the database instance, only one of the tempdb files had been created. I panicked, took a deep breath, checked the commands, and nothing was wrong. So I kindly asked the infra staff to reboot the server so I could try to start the sql server. In a miracle pass the BD went up and I finally managed to log in, the datafiles all created. It was one of the most terrifying moments of my life. Do not do this in a small window, schedule for weekends beforehand, SQLServer is not an exact science. Be warned, it seems to be a simple procedure but they do not know the risk they are taking, do not put their professional life at stake, schedule for a weekend.
I totally agree. Always try things out first on a Development server and do all the checks and balances before you try any configuration change.
I am glad that it worked out for you. Your story will help and motivated everyone to take enough time to do this task.
Very happy to know all is well.