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

SQL SERVER - TempDB is Full. Move TempDB from one drive to another drive. tempdb 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.

, , , , , , ,
Previous Post
SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)
Next Post
SQL SERVER – 2005 – Performance Dashboard Reports

Related Posts

78 Comments. Leave new

  • Amanda Morris
    July 8, 2009 7:19 pm

    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?

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

    Reply
  • * 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

    Reply
  • Thank you very much pinaldave!

    Reply
  • 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…

    Reply
  • 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…

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

    Reply
  • 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 ).

    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.

    Reply
  • 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…????

    Reply
  • 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)

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • AKHILESH MISHRA
    April 14, 2010 12:25 am

    Hi Pinal,

    I have checked the above script to move the temp db data/Log files to another location. It’s working fine.

    Reply
  • Thanks..

    Just want I was looking for..

    Reply
  • 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!

    Reply
  • 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?

    Reply
  • Hi Pinal, without restarting the sql server,is there any way to solve the tempdb full issue?

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

    Reply
  • Thanks for your great blogs. They’ve been a help not only once. Keep on the good work!

    Reply
  • 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…

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version