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.
79 Comments. Leave new
Hi,
I run into the above-mentioned error periodically, and have yet to find a satisfactory explanation for it.
I have 85 GB free on the partition where SQL is installed. I have ‘autogrow’ enabled for TempDB and no size restrictions.
I don’t think this is necessarily a performance issue, as the error has occassionally occurred during periods of very low site activity.
Any other suggestions on what could cause this error? I took a look in MS’s support site and MSDN, and couldn’t locate an official explanation to what causes this error.
Thanks,
Joel
Hello,
I, too, run into this problem.
I have the following config:
SQL2000
We have several DBs of varying sizes, (20 megs to 3 gigs to 6 gigs). The only DB to get hammered is the 3 gig one.
Temp DB is around 800 megs now.
I get these error messages consistently even though I tried setting the tempdb to 20% growth, etc.
I manually truncate the Tempdb (set to Simple mode) using the following:
BACKUP LOG tempdb WITH TRUNCATE_ONLY
GO
You can use this when you are UNABLE to truncate the log using other means. Why? Because backing up the log when its full requires the log to have space to record the backup in the Trans log. This side steps the issue.
The only idea I have been able to draw as to why the error codes keep appearing is that SQL reports the error and then grows the DB accordingly. This is just a guess because it would be nice for MS to have included a “growing tempdb” message in the logs.
BUT, I do have a couple questions:
I still am not quite getting the idea behind shrinking the DB after truncation. I am not a stellar DBA, so this concept has alluded me for some time.
Why shrink the db at all? Why not just leave it as is so there is automatically “room to grow”? I assumed that SQL is tuning the size of the tempDB and the current size before reboot indicates its general usage size.
Hi Pinal,
Is there any way to find why tempdb is growing.
I couldn’t see any reasons for it.
There is no open transactions running under tempdb or any sessions connected to it, but still the tempdb is growing
i have enough space for tempdb to grow, but want to know the reasons for it.
I am a rookie DBA :)
I have the tempDB eating away atleast 30GB. The log file is small but don’t understand why the data file is so big? Anyway I can shrink this? Thanks in advance.
NM
Right click on tempdb database and click on task and then click Shrink then Database.
Suppose there is one big database on drive c and Disk space of drive c is full so i want to move some tables from the databse on drive d.How we can do this
Thanks for ur quick response in advance
———-Vijay
hi Vijay,
If you want to move the table to some other drive steps you can follow are as follows :
1. create the fileGroup and files for that databse on the req drive. you can use alter database command , refer online help of that topic further explaination
2. create table on that filegroup whith some temp name
3. drop your original table
4. rename newly created table by your original table name
Joel, probably you have configured daily backups, checking database integrity, re-indexing or other operation that produce logs data.
Regards
Hi!
Pinal your blogs are very simple and I always seek help from them whenever get stuck or smethin :)
Keep it up :)
Just wanted to appreciate your work!
Regards!
On once server, tempdb always get’s full ..close to 65GB and get’s the drive full…We have to restart the sql server to flush it and tempdb files gets reduced to about 2-3 GB ..
I want to find out why this happens each week. How to troubleshoot..?
the answer may lie with in your application it is not uncommon for developers to use temporary tables and depending on the skill level these queries may not be written well which may cause the temp db to ballon
Hi Pinal,
I’m newbie SQLServer DBA.
I like your website, articles, blog…. everything.
I have a quick question:
How to move a database from c: drive to d:drive?
Would you please guide me.
Thanks in advance.
this is a cool sql server site.
@Priti
Right click database -> all task -> detach.
once you detach you cannot see that database any more in the database list.
move .mdf and .ldf files of the database you just detached from old location ( c:\drive) to new location (d:\drive).
now go to server again, right click on databases -> attach ,select the new location of the files. click ok,
Your database is ready and now the database files are in new d drive.
Hope this helps.
Thanks,
Imran.
Just restart the sql service. The temp mdf and ldf will get recreated and the size will be reduced automatically.
Hi,
I have SQL server 2000 enterprise installed.
The size of the tempdb mdf is 982mb and tempdb log 16mb.
There was enough space and the options are already set as you suggested.
Still it was giving the same message and not growing above this size.
The recovery model of my tempdb is SIMPLE and I was not able to set it to FULL.
Finally I tried explicitly setting the size of tempdb log to 100mb.
Then it simply worked.
My question is is there any limit for the size of this log file?
Why I am not able to change the recovery model of tempdb ?
thanks & regards
Thomas George
Hi all,
I am new in SqlServer.My application is running on sqlserver2000 with Windows2003 platform.I have some performance issue with my application.I know this is because of using many temporary table.Now we are not able to change the code of application.
Is it an solution to increasing the size of tempdb.If yes,How can we traced out the actual root cause of increasing tempdb size.Any script for that purpose would be appreciated.Also guide me how can I add datafile with the existing tempdb.
Thanks in advance
Vipin
Hi Pinal,
Same problem – been throwing resources at it like crazy. Current config: SQL2008 64-bit, 250GB of TEMPDB across 2 drives, 24 cores, but I still keep filling tempDB… by Database isnt even that big. What could I be doing wrong?
Thankyou
Hi all,
If you want to regain your disk space tempdb is using
simply stop the MSSQLSERVER Service from the Sql Server Configuration Manager next delete the tempdb mdf & ldf
files from
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
Now restart the MSSQLSERVER Service from the Sql Server Configuration Manager
your disk space is free
Hi all,
Yousuf Khan by deleting the files, do you mean removing the actual files called tempdb and templog from in the Data folder? If so would this cause a negative effect on the database ie something would stop working?
1) Open Sql server management studio.
2) Move tembdb to a drive having enough disk space
say d:/ by writing sql script
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘d:datatempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘d:datatemplog.ldf’)
GO
3) Close Sql server management studio.
4) Go to location
C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA
5) delete tempdb’s mdf & ldf files from above location.
6) Open Sql server management studio.