SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

« SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)

SQL SERVER – 2005 – Performance Dashboard Reports »

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

April 1, 2007 by pinaldave

If you come across following errors in log file, please follow the direction below.
Source: MSSQLSERVER 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 other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move 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 FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in 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 (http://blog.SQLAuthority.com) ,Microsoft Support, Database Journal.

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | Tagged SQL TempDB | 59 Comments

59 Responses

on April 9, 2007 at 9:33 am | Reply joel n.

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
on April 9, 2007 at 10:33 am | Reply pinaldave

Thanks Joel,

For your interest and bringing the problem up. I could not find any suggestion on MSDN so I came up with my own solution (as mentioned in blog) and fixed the issue. Our sever still sometimes gives the same error when there are very low usages also. Recently I just put small script in places which truncates the log on tempdb and this problem has stop happening. I think the same should help you also.

You can read the in detailed explanation here. http://blog.sqlauthority.com/2006/12/30/shrinking-truncated-log-file/

For easy reference I am typing the commands here.
use tempdb
dbcc shrinkfile(templog, 0)
backup log tempdb with Truncate_Only
dbcc shrinkfile(templog, 0)

Kind Regards,
Pinal Dave
(Author http://www.sqlauthority.com)
- on April 1, 2011 at 6:14 pm | Reply Bhupendra Singh
  
  Hi!
  I had a query regarding the moving TempDB to some other drive. Is it neccessay to stop IIS for this activity in case your whole website is on CMS driven ?
  
  Can it will be possible to move the same without stoping the IIS and putting the website down ?
  
  I am recently facing this problem for one of our website hosted.
  
  Please give your valuable adivise AEAP.
  
  Thanks & Regards
  Bhupendra Singh
  Sr.Developer
- on June 2, 2011 at 9:41 am | Reply madhu
  
  hi pinal
  
  i am wondering is there any way to fix tempdb full issue
  without restarting server.
  
  Thanks
  gidda
on April 26, 2007 at 1:41 pm | Reply JF

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.
on April 26, 2007 at 1:46 pm | Reply pinaldave

Thank you JF for your comments. You have all good points mentioned in the comment.

Answer to your question about : Why shrink the db at all? Why not just leave it as is so there is automatically “room to grow”?
The reason is sometime it has grown too much and for other application or other database user needs to re-gain the hard disk space. If space is not an issue leave it “room to grow” and that is actually better for the system as it does not have to grow every time when it fills up.

Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on July 7, 2007 at 2:50 am | Reply Raghu

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.
on July 17, 2007 at 10:17 am | Reply Nimesh

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
- on January 15, 2013 at 2:17 pm | Reply Rajesh Singh
  
  Right click on tempdb database and click on task and then click Shrink then Database.
on September 3, 2007 at 12:16 am | Reply Vijay

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
on September 14, 2007 at 2:58 am | Reply abhay

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
on April 11, 2008 at 11:27 pm | Reply Boichev

Joel, probably you have configured daily backups, checking database integrity, re-indexing or other operation that produce logs data.

Regards
on May 22, 2008 at 3:55 pm | Reply fimh

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 June 11, 2008 at 5:31 am | Reply vikram

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..?
on July 17, 2008 at 9:14 am | Reply tony

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
on July 26, 2008 at 12:15 am | Reply Priti

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.
on July 26, 2008 at 6:21 am | Reply mike

this is a cool sql server site.
on July 26, 2008 at 9:30 am | Reply Imran Mohammed

@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.
on September 23, 2008 at 12:53 pm | Reply Venu

Just restart the sql service. The temp mdf and ldf will get recreated and the size will be reduced automatically.
on January 26, 2009 at 1:24 pm | Reply Thomas George

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
on February 24, 2009 at 5:37 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive [...]
on February 26, 2009 at 6:13 pm | Reply Vipin

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
on March 21, 2009 at 1:45 am | Reply DaViD

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
on June 16, 2009 at 6:23 pm | Reply Yousuf Khan

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
on July 8, 2009 at 7:19 pm | Reply Amanda Morris

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?
on August 5, 2009 at 11:00 pm | Reply Yousuf Khan

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 Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

5) delete tempdb’s mdf & ldf files from above location.

6) Open Sql server management studio.
on August 5, 2009 at 11:22 pm | Reply Yousuf Khan

* One more technique to recover disk space used by old database on your system

1) Right click one particular database

2) Select detach

4) Go to folder where that particular database mdf and ldf
files are present

5) delete only the ldf file

6) Right click Databases in object explorer window of Sql server management studio. (Databases) is present as first item in the tree.

7) Click Attach…

8) Click Add…

9) GO to same database mdf file whose ldf you just deleted
in the locate database files window just opened
after clicking add button.

10) Two row gets just visible one for mdf other for ldf i.e. log file ,——–Click the log file row….2′nd row———–

11) Click the remove button

12) 2′nd row disappears

13) Click ok

By following above steps you delete old log files having size in giga bytes .

And allow the system to create a new log file for you of size
less than 600 KB

I have recovered 20 gb disk space by using this technique
on 7 databases
on September 3, 2009 at 12:33 pm | Reply VBNet

Thank you very much pinaldave!
on September 8, 2009 at 3:43 pm | Reply Joshua

good day sir,

how am i going to move the tempdb without using script? i am using mssql server 2005 sp3?

im a rookie in mssql

thanks…
on September 8, 2009 at 9:31 pm | Reply joshua

thanks for the info but i tried it, it doesn’t have the button where i can click and point to where the location i want to… please tell me too how to move the tempdb to ramdrive or memory i have 26GB memory installed in my server…

thanks…
on September 8, 2009 at 9:42 pm | Reply Steve Brower

Scratch that last post. I was thinking about ADDING tempdb files, not MOVING them. Sorry for the bad advice, my fingers are working faster than my brain this morning.

In addition to moving tempdb to a ramdrive I was also considering adding 3 additional files since my server is using two quad-core processors.
on September 8, 2009 at 9:49 pm | Reply Steve Brower

Joshua, I think that you would first need a program to carve out the space in memory as a dedicated drive. The ones I have found are SuperSpeed’s Ramdisk/Ramdisk Plus(http://www.superspeed.com/servers/ramdisk.php) and QSoft’s Ramdisk (http://members.fortunecity.com/ramdisk/RAMDisk/ramdriv001.htm).

The Superspeed product is kind of expensive, so I was thinking of using the QSoft software.

I just ordered the additional memory last week, so I haven’t had a chance to get this setup yet. I can post a follow-up once I’ve gotten my feet wet with this a bit.
on October 14, 2009 at 3:15 pm | Reply Ahmad Bakri

hi,
Our ERP system using sql server 2000 and when running huge reports that gives much info. the server processor get 100% from the sql server.

And this make the application too slow and then the reports take long time to generate.

Sql server doesn’t give any error but when stop and start the service it goes good till many reports genarated again.

I think i have problem in the tempdb.

Tempdb is auto grow and there is much disk space .about 40GB.

and its size about 200MG.

Please help ? what to do ? I trying to solve the issue since weeks…????
on February 22, 2010 at 8:45 pm | Reply Pablo Llano

Hello, The tempdb database grows out of control when I run a command EXEC (@ SQL) in a SP in SQL Server 2000.

@ SQL =”INSERT Table_temp SELECT Column1, column2 ….. FROM table1 INNER JOIN Table 2 … etc. “.

How can I fix this?

Thanks

Pablo (Sorry, I’m Chilean and speak only Spanish)

(Hola, La base de datos Tempdb crece sin control cuando ejecuto una instrucción EXEC (@SQL) en un SP en SQL Server 2000.

@SQL = ”INSERT Table_temp SELECT Column1, column2….. FROM table1 inner join Table 2… etc.”

¿Como puedo solucionar esto?

Gracias)
on March 18, 2010 at 8:59 pm | Reply levi

newb with SQL.

is the tempdb the same as the transactional log?
how can i move the transactional logs to a different drive from that used by the database?
on March 29, 2010 at 5:05 pm | Reply Shakir

Hi guys!
I have the same issue as many of you guys, the TempDB gets full during a huge insert into and from a temp table.

The problem is solved by either restarting the SQL Server or shrink the tempdb.

BUT :) what happens if I instead deselecting the auto-grow for the tempdb and set the size to a maximum?

If the tempdb reaches the maximum size will it auto delete or shrink?
on April 14, 2010 at 12:25 am | Reply AKHILESH MISHRA

Hi Pinal,

I have checked the above script to move the temp db data/Log files to another location. It’s working fine.
on May 25, 2010 at 2:49 pm | Reply Sivan

Thanks..

Just want I was looking for..
on July 2, 2010 at 9:44 am | Reply Dave

Hi,
Moving the TempDB to another drive is a good thing, but doesn’t address the root cause of the DB growing quickly.

Restarting the DB all the time is also not practical when you have a 24/7 system running.

Thomas George: according to the MS support site, tempDB cannot have its recovery model changed, its always simple.

Shakir: if you set the TempDB to not autogrow, then it will just fill up. Same with setting a maximum size. These ideas do not resolve the problem.

The only way to instantly reduce the size of the tempDB and its log is to restart the SQL service. Do not delete the files, there is no point, restarting the service will create new tempDB & its log file with small size anyway.

The real solution is to find the Root cause of what is causing the tempdb to grow in the first place. Its going to be some queries, or race conditions, or pending transactions. I don’t really know the answer. I am going to try using the profiler on the server to monitor various queries and see how the tempDB grows. I can’t see any other easier way.

Help!
on July 12, 2011 at 7:03 am | Reply SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

[...] TempDB is Full. Move TempDB from one drive to another drive [...]
on August 11, 2011 at 5:49 pm | Reply Ajay

Pinal,

I have made four Temp Db files with 8192.000000 MB and i want reduce the size of all four file or delete it up want to recreate with 8 MB of size. Can you Please help me out-there?
on September 2, 2011 at 12:16 pm | Reply Jothiram

Hi Pinal, without restarting the sql server,is there any way to solve the tempdb full issue?
on November 7, 2011 at 5:01 pm | Reply guest

How could i check why TempDB is growing so much that it fills the all c:\ . I don’t want to move or shrik it just to know why it’s growing so much.
on November 16, 2011 at 3:12 pm | Reply Sven F.

Thanks for your great blogs. They’ve been a help not only once. Keep on the good work!
on November 23, 2011 at 11:11 pm | Reply Jaco van Wyk

Make sure that you get the file structure exactly right with this. My SQL Service didn’t want to restart because I specified a wrong directory. Looked in Event Viewer and got “FCB::Open failed: Could not open file” as the error.

I had a hell of a time tracking a solution to change my tempdb location without SSMS but found this post. It saved my life…

http://sql.richarddouglas.co.uk/archive/2010/08/recovering-tempdb.html
- on December 16, 2011 at 1:30 pm | Reply pinaldave
  
  Richard Douglas is excellent blogger and I am big fan of his writing. Thanks Jaco for the link.
on March 23, 2012 at 11:07 pm | Reply Raja Adepu

Hi Guys..,

There is no way to reduce the tempdb size without restarting…? Please reply
on April 13, 2012 at 7:34 pm | Reply khalid

it helped me too.
on June 7, 2012 at 11:45 am | Reply ali

can i after that delete the original tempdb files from c:\ ?
on June 12, 2012 at 10:59 pm | Reply Kirtan

Hello Pinal,

How does one move more than one TEMPDB mdf file to another location?
Can you provide the script?
- on June 18, 2012 at 1:54 pm | Reply madhivanan
  
  Did you try alter database command?
on October 17, 2012 at 7:25 pm | Reply John Boulom

Simple solution. Thanks!
on December 21, 2012 at 3:36 pm | Reply JK

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.
on January 17, 2013 at 2:40 am | Reply Jon Lee

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??
on February 13, 2013 at 10:54 am | Reply Syed Asrar Ul Haq

Hi Pinal,
Can I move tempdb to external usb harddisk ? I cannot extend server disk because its 2003 R2 ..

Thanks
Asrar
on March 19, 2013 at 8:30 am | Reply Notes for Disk Space Cleanup on MS Windows 2008 R2 | SZemtsov Technical Roll

[...] sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive [...]
on April 6, 2013 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

[...] TempDB is Full. Move TempDB from one drive to another drive 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. [...]
on April 11, 2013 at 12:22 am | Reply Tarun

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.
on April 18, 2013 at 11:16 am | Reply Murali

Can we use the shrik tempdb while executing sql query in another session.
dbcc shrinkfile(templog, 0)