February 6, 2010 at 12:48 pm
Hi,
I have followed the below steps to move the Master database log file mastlog.ldf to a different drive.
But I did NOT perform the last step (Step 7). But ldf file of Master database has moved to different drive successfully(I have performed up to step6 and then started the SQL Service and everything is fine). So I did not understand in what scenarios STEP7 has to be performed.
Please advice me...
To move the master and Resource databases, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
5. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
6. Move the master.mdf and mastlog.ldf files to the new location.
7. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
* For the default (MSSQLSERVER) instance, run the following command.
NET START MSSQLSERVER /f /T3608
* For a named instance, run the following command.
NET START MSSQL$instancename /f /T3608
Thank you
February 6, 2010 at 12:53 pm
thats leading on to moving the resource database. The master and resource databases should be kept together.
do yourself a big favour, move the master log file back where it was with the data file. Master database has very little log activity being rarely updated so there is no point in separating out its data and log.
---------------------------------------------------------------------
February 7, 2010 at 9:22 pm
7th step is not required in ur case
it is required while restoring master database backup.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 8, 2010 at 5:23 am
I agree with Sanket that 7th step is not required.
1) Trace flag is used to detach system databases like model,msdb
2) Trace flag 3608 prevents SQL Server from recovering any database except the master database.
VIMP : dont forget to remove the the trace flag 3608 once you are done moving of system databases.
February 8, 2010 at 10:50 pm
george sibbald (2/6/2010)
do yourself a big favour, move the master log file back where it was with the data file. Master database has very little log activity being rarely updated so there is no point in separating out its data and log.
George, could be his business requirement that all the log files should placed collectively on one drive.
February 9, 2010 at 3:44 am
I agree with George.
All site requirements (should) exist to improve reliability, availability or performance. Any sensible set of site requirements should include a requirement to leave the for master, resource and model databases where they are installed. There are risks involved in moving them, and no benefits to gain by doing the work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 9, 2010 at 5:50 am
Krishna Potlakayala (2/8/2010)
george sibbald (2/6/2010)
do yourself a big favour, move the master log file back where it was with the data file. Master database has very little log activity being rarely updated so there is no point in separating out its data and log.George, could be his business requirement that all the log files should placed collectively on one drive.
I agree with Ed agreeing with me. I cannot iterate enough what a waste of time the practice of separating out system db log files is. (Won't get into msdb\tempdb discussion here).
If any DBA was given such a 'business' requirement they should politely but firmly explain why it was unnecessary and a bad idea.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply