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.
i did run the above script. How long does it usually take when you encounter such a problem?
Hi Pinal, Plz Suggest.. as per article >>> however, no changes are made to TempDB till SQL Server restarts… What to do if its peak hours…?
There is no other choice than restart. That’s why planning is important so that this doesn’t appear at the first place.
you can add a new data-file in that case to different filegroup.. it will not require any restart and no impact on busy prod server….
Are you talking about TempDB.. Its not possible. Here is the error, if you try adding new FG.
Msg 1826, Level 16, State 1, Line 3
User-defined filegroups are not allowed on “tempdb”.
Planning to move tempdb file from one file to another file .. Current drive having only 32 GB space and moving it into new drive which is having 70GB. I have just ran the Modify scripts for tempdb which is saying initial size 4 GB and auto growth 1 GB. total 8 files. as soon as i ran this alter statement my existing tempdb drive got full. is it normal behavior ,sql server hold new intial size in current database?
Pinal, We could add another data file in different drive which has more space to resolve the issue.