Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving System Databases


Moving System Databases

Author
Message
NetEng81
NetEng81
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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.
NetEng81
NetEng81
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24200 Visits: 37964
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.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24200 Visits: 37964
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');
GO


Change the location of each file by using ALTER DATABASE.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO


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.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
NetEng81
NetEng81
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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.
clalc
clalc
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 107
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 ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search