登入
Microsoft.com
 
Microsoft
 
 
 

none
Cannot access SQL Server configuration manager after relocating master and system resource database in SQL Server 2005 RRS feed

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票

     

    Hi

     

    After relocating the master and systemreousrce database in SQL server 2005 to a new

    disk drive, we are unable to use SQL Server configuration manager. A dialog box

    with the message saying that SQL Server configuration managger cannot connect to the target

    in a timely fasion (not fashion). 

     

    SQL Management Studio also unable to load the status of the database and SQL server agent.

    The little white ball which suppose to show and green 'play' triangle next to the database

    and SQL server agent in the management studio are now blank even they are running.

     

    The SQL Server is functioning properly, all applications that rely on the SQL server are working

    properly. sp_who shows that users are connecting to the database.

     

    Any help is very much appreciated.

     

    Thanks.

     

    2008年10月11日 下午 03:08
    |

解答

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    Would you please describe exactly all of the steps that you had done to move the (master, mssqlsystemresource) databases?

     

    Are there any error messages from the SQL Server log files?

    2008年10月11日 下午 04:47
    |
  • Question
    登入以投票
    0
    登入以投票

     

    Master database: change startup configuration in SQL Server configuration manager, change -d, -l, and -e to the new drive, shutdown the database and move the master db files to the new drive

     

    Systemresource: start the SQL server in trace mode /T3608 /f /c, then use alter database to specify a new location.

    Shutdown the database and move the systemresource db files to the new drive. Start in trace mode again and set the systemresource database to read only.

     

    I find the following in the log file:

    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install

    Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    2008年10月12日 上午 09:34
    |
  • Question
    登入以投票
    0
    登入以投票
    Those configuration logs shouldn't affect anything here. Did you restart sql without those options after moving resource db?

     

    2008年10月12日 下午 09:20
    |
  • Question
    登入以投票
    0
    登入以投票

     

    You mean starting the sql server without the /T 3608 /f /c options?

     

    Yes. Indeed, we are running in a MS cluster environment and the SQL database need to be started

    using the command line "sqlservr /t 3608 /f /c" in order enter single user mode. After altering the

    systemresource database, I used CTRL-C to stop the SQL server and move the systemresource

    db files. Then, start using "sqlservr /t 3608 /f /c" again to set the systemresource db into read only mode,

    use CTRL-C to shutdown again and startup again using the cluster admin GUI.

     

     

     

    2008年10月13日 上午 01:14
    |
  • Question
    登入以投票
    0
    登入以投票

    You did final restart without /T 3608 /f /c options?

    2008年10月13日 下午 05:19
    |
  • Question
    登入以投票
    0
    登入以投票

    Yes. The final restart is without any /T /f or /c options.

     

    I didn't mofidy the service startup parameters with "-t3608;-c;-m" at all. I run the SQL server from command line into single user mode with these options. When I start SQL server in MS cluster GUI, it start as a normal SQL server.

     

     

     

     

     

    2008年10月14日 上午 04:16
    |
  • Question
    登入以投票
    0
    登入以投票
    Can you start configuration manager in ssms?

     

    2008年10月14日 下午 01:54
    |
  • Question
    登入以投票
    0
    登入以投票
     rmiao wrote:
    Can you start configuration manager in ssms?

     

     

     

    Thanks all for the help.

     

    It seems that the systemresource database was not moved successfully. I can start SQL server without the old disk drive online and I thought that the database was already moved.

     

    Today, I tried to reboot the server and after that, finally, the errorlog shows that there was error in loading the systemresource database which is still pointing to the old location. I repeat the process to move the systemresource database and succeeded.

     

    I did able to stop/start sql server successfully with the old LUN being offline in cluster admin and thought that all db were moved.

    2008年10月14日 下午 02:08
    |