Post reply

How to move tempdb.mdb off of drive c:

  • June 18, 2008 at 3:02 pm

    #188023

    This is my First post.

    Anyway, I know very little about SQL server 2005. I know enough to tweak it here and there, but I have this strange issue I really want resolved.

    I built a custom SQL 2005 box. Q6700, 4gig of RAM, 8 x 15K RPM 74 gig SAS drives on a 3ware 9690SA-8I controller, Two RAID 1's and one RAID 5. I'm using a Supermicro case, with 8 hot swappable draws. I'm able to see which drives get written too which I like.

    First RAID 1, is the boot/OS, Second RAID 1 is setup as two 33 gig partitions for logs files, The RAID 5 is one partition for the database.

    Everything seems to be functioning. My database is almost 400K part numbers.

    Here is the problem: When I read the database for the first time, you clearly see it read from the RAID 5 array, but when you select a different task, and then go back, it clearly reads from drive C:. Why? And how can I change this?

    Here is a video of what I'm talking about. Easier to see it, than read it.

    The top two LEDs are drive C:(RAID 1), the next two are the logs (RAID 1) , and the next four are RAID 5, dataabse.

    So maybe you guys can help me in this matter.

    Thanks

    Joe

    Edit: I ran FILEMON.exe, and found that it is writing to a file located on drive C called tempdb.mdf.

    How can I change this to write to a different location?

  • June 19, 2008 at 12:46 am

    #830914

    You can do it via a SQL query.

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME = < New directory>\tempdb.mdf');

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = templog, FILENAME = < New directory>\templog.ldf');

    Once you've run that, restart the SQL instance and TempDB will be in the new location.

    I's usually recomended that TempDB is placed on a dedicated, fast drive, especially is the app uses tempDB a lot.

    TempDB is SQL's temporary work space. It's used for temp tables and table variables, for work tables created during query processing and for a few other internal operations.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 19, 2008 at 9:28 am

    #831190

    Is that the exact syntax?

    The new directory would be X:\SQLtemp\data and X:\SQLtemp\logs for logs.

    Remember I know nothing about SQL, so make it as simple as you can.

    Thanks

  • June 19, 2008 at 9:46 am

    #831206

    I figured it out...until I did not get an error... 😉

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = templog, FILENAME = 'X:\SQLtemp\logs\tempdb.ldf');

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQLtemp\data\tempdb.mdf');

  • June 20, 2008 at 9:30 am

    #831892

    You may also want to check and see that the old tempdb .mdf and .ldf files are cleaned up (deleted) as well.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 24, 2008 at 9:04 am

    #876440

    Gail,

    Thank you!!! for this post, it just got us out of a jam with the server falling over because tempdb on C drive was eating up all available disk space due to a runaway data mining operation. D had plenty of space, so again, thank you for this post!

    --adam cassel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply