MS SQL Server Connectivity - Lost connection to sql server

Author

Lost connection to sql server

2005-06-23, 3:23 am

Hi,
I just installed a virtual sql server 2000 sp3a named instance on a MSCS
cluster. I was trying to get the log files onto a separate LUN in my SAN. I
changed the startup parameters in Enterprise manager to point to a new
location for the -l parameter (log) and removed the previous -l parameter. I
then stopped the server and copied the mastlog.ldf to my new location. I
then tried to restart the server, and cannot - bad connection string. I have
tried going to the registry to HKLM\SOFTWARE\Micros
oft\Microsoft SQL
Server\SQLINSTNAME\M
SSQLServer\Parameter
s and changing the SQLArg2 value back
to the old path to the log file. However, this is not working. In fact,
everytime I try to restart the server, this registry parameter is reset to
the BAD connection string. I suppose this is coming out of the master db
itself? Unfortunately, I do not have a backup of this. What are my options?
Also, for next time, what is the proper way to relocate these log files?
Thanks.
--
John

Peter Yang [MSFT]

2005-06-23, 7:23 am

Hi John,

Based on my experience, the steps you use are correct even on cluster
server.

224071 Moving SQL Server databases to a new location with Detach/Attach
<http://support.microsoft.com/?id=224071>

Though master database file location are also stored in sysdatabases table,
it is usually not used at startup.

When SQL service starts, it looks in the registry for the Master database
file locations, which is defined by the properties in EM. After Master
database comes up, it looks into the sysdatabases table for data file
locations of other databases. So, only the location of the Master database
in this table is not used. All other values have to be valid.

Cluster configuration database is stored in the registry on each node, with
another copy maintained in a checkpoint file on the quorum drive. Changes
to the cluster configuration are not written into the checkpoint file at
the same time as the node registries are updated (default every four
hours).

When the registry gets modified manually, not from cluster admin console,
it will take 4 hours to update the quorum checkpoint file. If in this
period of time the virtual SQL server is taking offline/online, the SQL
server will use the configuration database in the quorum checkpoint file
and the registry will be overwritten right away. The 4 hours is oneway
traffic, only from registry to checkpoint file, the other way is
immediately.

You could locate the SQL Server Resource in the Cluster Hive under the
Resources Key.Then drill down to the Parameters key under the SQL Server
Resource.We should see the following two values under the Parameters Key.

(HKLM\Cluster\Resour
ces\{GUID for Resource}\Parameters
\)
Default Instance
Name: InstanceName Type: Reg_SZ Data: MSSQLSERVER
Name: VirtualServerName Type: Reg_SZ Data: Virtual SQL Server Name
Named Instance
Name: InstanceName Type: Reg_SZ Data: Instance Name of the
SQL
Server
Name: VirtualServerName Type: Reg_SZ Data: Virtual SQL Server Name

Also, you could try running from command prompt.

%ProgramFiles%\Micro
soft SQL Server\MSSQL\Binn\sq
lservr.exe -s<instance
name> -l <original master database log file location>

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Thread-Topic: Lost connection to sql server
| thread-index: AcV3tC9wrfBBbXdaQ0m4
fa6W9EoYGA==
| X-WBNR-Posting-Host: 24.218.199.128
| From: "=?Utf-8?B?SlQ=?=" <Jthayer@online.nospam>
| Subject: Lost connection to sql server
| Date: Wed, 22 Jun 2005 22:27:02 -0700
| Lines: 17
| Message-ID: <B795A945-F63E-4CB9-9A31- 253699A28FC1@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.connect:4301
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Hi,
| I just installed a virtual sql server 2000 sp3a named instance on a MSCS
| cluster. I was trying to get the log files onto a separate LUN in my
SAN. I
| changed the startup parameters in Enterprise manager to point to a new
| location for the -l parameter (log) and removed the previous -l
parameter. I
| then stopped the server and copied the mastlog.ldf to my new location. I
| then tried to restart the server, and cannot - bad connection string. I
have
| tried going to the registry to HKLM\SOFTWARE\Micros
oft\Microsoft SQL
| Server\SQLINSTNAME\M
SSQLServer\Parameter
s and changing the SQLArg2 value
back
| to the old path to the log file. However, this is not working. In fact,
| everytime I try to restart the server, this registry parameter is reset
to
| the BAD connection string. I suppose this is coming out of the master db
| itself? Unfortunately, I do not have a backup of this. What are my
options?
| Also, for next time, what is the proper way to relocate these log files?
| Thanks.
| --
| John
|

Sponsored Links