Moving System Databases

  • Hi Vince,

    I am fairly new to SQL Server 2005 Administration. I am running low on disk space on my C: drive where all the system databases recide. Can you please help me with the script so I may move the files to an alternate drive? What changes do i need to make?

    All my system database files (primary data file as well as log files) are located under the default location:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    I need to move the data files under D:\program Files\Microsoft SQL Server\Data folder and the log files under D:\program files\Microsoft SQL Server\Logs folder.

    All the databases that I created already locate on a separate drive (on the SAN).

    I am not very confident in the process of making this chance. Any help would be appreciated.

    Thank you.

  • More information:

    Directory of C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    07/21/2008 01:37 PM .

    07/21/2008 01:37 PM ..

    04/24/2008 10:44 AM 517 AACDB15D-DE28-45AC-ACE8-0A004AA399D7.cer

    05/20/2008 03:31 PM 517 CEFF3CF4-C74A-43CF-897F-1064D228A6A0.cer

    02/10/2007 12:49 AM 2,883,584 distmdl.ldf

    02/10/2007 12:49 AM 5,242,880 distmdl.mdf

    07/21/2008 01:37 PM 0 list.txt

    07/17/2008 09:04 PM 4,194,304 master$4IDR

    07/19/2008 09:46 AM 4,194,304 master.mdf

    05/20/2008 03:46 PM 786,432 mastlog$4IDR

    05/20/2008 03:46 PM 786,432 mastlog.ldf

    07/17/2008 09:04 PM 1,245,184 model$4IDR

    07/19/2008 09:46 AM 1,245,184 model.mdf

    07/17/2008 02:00 AM 3,538,944 modellog$4IDR

    07/21/2008 01:09 PM 3,932,160 modellog.ldf

    07/19/2008 10:00 PM 9,043,968 msdbdata.mdf

    05/20/2008 03:46 PM 786,432 msdblog.ldf

    02/10/2007 12:39 AM 524,288 mssqlsystemresource.ldf

    02/10/2007 12:39 AM 40,173,568 mssqlsystemresource.mdf

    07/19/2008 09:46 AM 3,342,336 ReportServer.mdf

    07/19/2008 09:46 AM 2,293,760 ReportServerTempDB.mdf

    05/20/2008 03:46 PM 786,432 ReportServerTempDB_log.LDF

    05/20/2008 03:46 PM 786,432 ReportServer_log.LDF

    07/20/2008 12:02 AM 831,062,016 tempdb.mdf

    07/20/2008 12:03 AM 36,569,088 templog.ldf

    23 File(s) 953,418,762 bytes

    2 Dir(s) 13,688,832 bytes free

  • The easiest system database to move is tempdb. If memory serves me, there is actually an example in BOL on how to move tempdb. It does require a restart of SQL Server. That alone looks like it would free up a lot of space on your C: drive.

    I'll see if I can find it, but drop a post if you find it first.


  • Here is what I found in BOL.

    G. Moving tempdb to a new location

    The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

    Determine the logical file names of the tempdb database and their current location on disk.

    SELECT name, physical_name

    FROM sys.master_files

    WHERE database_id = DB_ID('tempdb');


    Change the location of each file by using ALTER DATABASE.

    USE master;



    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');



    MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');


    Stop and restart the instance of SQL Server.

    Verify the file change.

    SELECT name, physical_name

    FROM sys.master_files

    WHERE database_id = DB_ID('tempdb');

    Delete the tempdb.mdf and templog.ldf files from their original location.

    Hope this helps you out some. I haven't gone through the process of moving msdb or master yet.


  • Ahh okay!

    Thanks you very much.

    Yes, the biggest culprit is indeed the temp database.

    Since i am very low on disk space (13 MB), i will eventually want to move the other system databases as well.

    thanks for the help. I think for now, simply moving the temp database should help.

  • I have seen in different places different drives for ALTER DATABASE part some of them list E:\... and E:\... some of them are listing E:\.... and F:\.... Which one is correct ?

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

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