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

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

69 thoughts on “SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

  1. 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

    Like

  2. 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)

    Like

    • 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

      Like

  3. 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.

    Like

  4. 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)

    Like

  5. 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.

    Like

  6. 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

    Like

  7. 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

    Like

  8. 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

    Like

  9. 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!

    Like

  10. 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..?

    Like

  11. 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

    Like

  12. 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.

    Like

  13. @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.

    Like

  14. 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

    Like

  15. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  16. 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

    Like

  17. 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

    Like

  18. 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

    Like

  19. 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?

    Like

  20. 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.

    Like

  21. * 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

    Like

  22. 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…

    Like

  23. 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.

    Like

  24. 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.

    Like

  25. 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…????

    Like

  26. 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)

    Like

  27. 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?

    Like

  28. 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?

    Like

  29. 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!

    Like

  30. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  31. 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?

    Like

  32. 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.

    Like

  33. 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.

    Like

  34. 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??

    Like

  35. Pingback: Notes for Disk Space Cleanup on MS Windows 2008 R2 | SZemtsov Technical Roll

  36. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  37. 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.

    Like

  38. 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.

    Like

  39. 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.

    Like

  40. 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.

    Like

  41. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s