July 21, 2008 at 11:44 am
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.
July 21, 2008 at 11:46 am
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
July 21, 2008 at 12:06 pm
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.
😎
July 21, 2008 at 12:11 pm
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.
😎
July 21, 2008 at 12:16 pm
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.
June 2, 2010 at 11:09 am
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