Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving System Databases Expand / Collapse
Author
Message
Posted Monday, July 21, 2008 11:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2008 8:35 AM
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.
Post #537865
Posted Monday, July 21, 2008 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2008 8:35 AM
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
Post #537866
Posted Monday, July 21, 2008 12:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:45 PM
Points: 23,081, Visits: 31,620
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.




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)
Post #537885
Posted Monday, July 21, 2008 12:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:45 PM
Points: 23,081, Visits: 31,620
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.




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)
Post #537890
Posted Monday, July 21, 2008 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2008 8:35 AM
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.

Post #537897
Posted Wednesday, June 2, 2010 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:00 AM
Points: 2, Visits: 93
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 ?
Post #931526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse