MSSQLWIKI

Karthick P.K on SQL Server

Archive for April, 2009

How to add an IP Address when we Add new NIC to node where SQLServer2005 instance is running.

Posted by Karthick P.K on April 14, 2009

The only possible way to add an IP to SQLServer2005 after adding new NIC to the
node where SQLServer2005 is installed (or) After adding second ip address to
existing NIC is to either manually edit the registry or run Service pack/Hotfix setup again.
The only possible way to add an IP to SQLServer2005 after adding new NIC to the
node where SQLServer2005 is installed (or) After adding second ip address to
existing NIC is

Option1

Manually edit the registry

1. Add a new registry key under
HKLM\software\microsoft\microsoftSQLServer\mssql.1\mssqlserver\supersocketnetlib\tcp
for IP2

simplest way to make it is export the Key

HKLM\software\microsoft\microsoftSQLServer\mssql.1\mssqlserver\supersocketnetlib\tcp
,

Open the exported file using the notepad and Copy the Following string ,make
required changes(Modify the IPAddress,IPn.. Ie: n stands for number of IP and MSSQL.N Ie nstands for instance ID )

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\Tcp\IPn]
“Enabled”=dword:00000000
“Active”=dword:00000001
“TcpPort”=””
“TcpDynamicPorts”=””
“DisplayName”=”Specific IP Address”
“IpAddress”=”65.52.17.19”

2. Import the registry again.

3..Now the new ip is listed in SQLServer configuration manager for editing.
Option2

Install the service pack again.

1. After the installatin of Service packs/HotFIx SQLServer recognize the new NIC but
Assings wrong IPaddresses to SQLserver.it makes SQLServer Listen on wrong ip’s and
so the SQLServer might fail to start with error

“Error: 17120, Severity: 16, State: 1.
2007-08-25 00:27:10.83 Server SQL Server could not spawn FRunCM thread.

Check the SQL Server error log and the Windows event logs for information about
possible related problems.”

2. We can find duplicate IP in SSCM(SQLServer Configuration manager). we can edit
the IP addresses in SQLserver configuration manager to fix right IP and start the SQLServer.

Regards

Karthick P.K

Posted in Configuration, SQL Server Tools | Tagged: | 4 Comments »

Different Status bits of sysdatabases in SQLServer

Posted by Karthick P.K on April 11, 2009

Sysdatabases.status and sysdatabases.status2 Bit of Sysdatabases in SQLServer
======================================================

<Script1>

declare @status int;
declare @status2 int;

Set  @status=’65544′  –Replace your DB status here
set @status2=’1090520064′ –Replace your DB status2 here

SELECT CASE (@status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (@status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (@status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (@status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (@status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (@status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (@status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (@status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (@status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (@status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (@status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (@status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (@status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (@status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (@status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (@status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (@status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (@status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (@status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (@status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (@status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (@status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (@status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (@status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled

</Script1>

 

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

<Script2>

SELECT substring(name, 1, 50) as dbname, cmptlevel, filename, version, CASE (status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled FROM sysdatabases where name = ‘master’
go
SELECT fileid, name, filename, size, maxsize, growth, groupid
FROM sysaltfiles WHERE dbid IN ( SELECT dbid FROM sysdatabases where name = ‘ Replace DB Name’ )
go
SELECT fileid, name, filename, size, maxsize, growth, groupid FROM sysfiles
go
SELECT groupid, groupname, CASE (status & 0x8) WHEN 0x8 THEN 1 ELSE 0 END AS readonly,
CASE (status & 0x10) WHEN 0x10 THEN 1 ELSE 0 END AS isdefault FROM sysfilegroups WHERE groupid IN ( SELECT groupid FROM sysaltfiles a, sysdatabases b WHERE a.dbid = b.dbid AND b.name = ‘Replace DB Name’ )

</Script2>

 

Regards

Karthick P.K

Posted in SQL General | Tagged: , , , | 7 Comments »

Installation of SQLServer2008 fails (The registry key SYSTEM\CurrentControlSet\Services\RsFx0102\InstancesShares is missing)

Posted by Karthick P.K on April 10, 2009

Installation of SQLServer2008 might fail with below error

Detailed results:
Feature:                       Database Engine Services
Status:                        Failed: see logs for details
MSI status:                    Passed
Configuration status:          Failed: see details below
Configuration error code:      0xCD263ADC@1306@30
Configuration error description: The registry key SYSTEM\CurrentControlSet\Services\RsFx0102\InstancesShares is missing.
Configuration log:             C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090410_153833\Detail.txt

To resolve this issue:

Create a new KEY with name   InstancesShares under registry path

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet\Services\RsFx0102\

Uninstall the failed Database engine components and reinstall the SQLServer 2008 instance after restarting the system.

Regards

Karthick P.K

Posted in SQL General | Tagged: | 2 Comments »

 
%d bloggers like this: