C Drive Full & guess where SQL Server was installed – Aargh!

It has been a while since I blogged on those “gotchas” that I come across as a SQL Server DBA.

I still acquire already installed & configured SQL Server database systems after an outage brings them to my attention. A more common scenario than desirable is the C Drive filling and impacting the SQL Server installation resident on this same drive. A colleague, James Donnelly, came up with the following script to help move the critical system databases to another drive. This example does assume a default instance rather than a named instance of SQL Server.

1 open command prompt & type the following

NET STOP MSSQLSERVER

2 once complete – start it up again with the /f /T3608
NET START MSSQLSERVER /f /T3608

3 open SQL connection to DB & run the following commands, This will identify where the current location of the mdf & ldf files are located. Open up an Explorer window to this location & verify that the files exist

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

4 Execute the following scripts against the Master db.

— Master DB
ALTER DATABASE master MODIFY FILE( NAME = master , FILENAME = ‘E:\MSSQL\Data\master.mdf’ )
ALTER DATABASE master MODIFY FILE( NAME = mastlog , FILENAME = ‘E:\MSSQL\Log\mastlog.ldf’ )

— Model DB
ALTER DATABASE Model MODIFY FILE( NAME = modeldev , FILENAME = ‘E:\MSSQL\Data\model.mdf’ )
ALTER DATABASE Model MODIFY FILE( NAME = modellog , FILENAME = ‘E:\MSSQL\Log\modellog.ldf’ )

— MSDB DB
ALTER DATABASE MSDB MODIFY FILE( NAME = MSDBData , FILENAME = ‘E:\MSSQL\Data\MSDBData.mdf’ )
ALTER DATABASE MSDB MODIFY FILE( NAME = MSDBLog , FILENAME = ‘E:\MSSQL\Log\MSDBLog.ldf’ )

— TEMPDB
ALTER DATABASE TEMPDB MODIFY FILE( NAME = TempDev , FILENAME = ‘E:\MSSQL\Data\TEMPDB.mdf’ )
ALTER DATABASE TEMPDB MODIFY FILE( NAME = TempLog , FILENAME = ‘E:\MSSQL\Log\TEMPLog.ldf’ )

5 Open CMD prompt & run the following:-
NET STOP MSSQLSERVER

6 Move the following files from the location identified in 3 to the new locations identified in 4

7 Launch SQL Server Configuration Manager (Start-> Programs -> SQL 2012 ->Config Tools -> SQL Server Configuration Manager

Select SQL Server Services & right click on SQL Server (MSSQLSERVER) & select Properties

Select each of the above values for master.mdf & mastlog.ldf files & change them to be the location identified in 4
** Also change the ERRORLOG value to be the MSSQL\Log location.
Select Apply & then OK to close the properties window. Select OK to confirm changes will not take effect until server is restarted.

8 Go to CMD prompt again & type the following command
NET START MSSQLSERVER
** SQL Server should be started now.

9 Execute the following SQL to confirm that all the DB’s have moved
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: