Daily Archives: February 1, 2017

Standard

Posted by

Posted on

February 1, 2017

Posted under

Setup

Comments

1 Comment

SQL services fails after applying service pack

-> I applied service pack on one of the SQL instance in a 2 node cluster. After applying the service pack, SQL services failed to come online.

-> I applied service pack on passive node first and then on the active node. The install went fine without any issues. But after the service pack upgrade SQL server did not start.

-> Checked the SQL server error log and found that the SQL server fails during script upgrade mode with below error.

2016-12-08 23:57:25.43 spid8s      DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2016-12-08 23:57:25.43 spid8s      Error: 537, Severity: 16, State: 3.
2016-12-08 23:57:25.43 spid8s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2016-12-08 23:57:25.43 spid8s      Error: 912, Severity: 21, State: 2.
2016-12-08 23:57:25.43 spid8s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’encountered error 537, state 3, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-12-08 23:57:25.43 spid8s      Error: 3417, Severity: 21, State: 3.
2016-12-08 23:57:25.43 spid8s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-12-08 23:57:25.43 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
2016-12-08 23:57:25.46 Logon       Error: 18401, Severity: 14, State: 1.
2016-12-08 23:57:25.46 Logon       Login failed for user ‘iuser_SitefinityFrontier’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 66.133.172.174]

-> Added -T902 to stop script upgrade mode. Started the SQL services, it started without issues. Checked the owner for MSDB and Master. Both were having SA as owner.

902 Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack.

-> Searched for sqlagent100_msdb_upgrade.sql file and opened it.

-> The script upgrade mode was failing on below set of statement in file sqlagent100_msdb_upgrade.sql,

SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N’master.mdf’, LOWER(filename)) – 1)  FROM master.dbo.sysaltfiles
WHERE (name = N’master’)

-> The SQL server instance did have more than one database with logical name “master”.

-> Query “”SELECT db_name(dbid) FROM master.dbo.sysaltfiles WHERE (name = N’master’)”” returned 3 databases including master database in my SQL server instance.

-> Variable @device_directory can contain only one value. But in our case 3 values were returned and thats the reason we got this error.

-> I detached the 2 databases.

-> Ran Query “”SELECT db_name(dbid) FROM master.dbo.sysaltfiles WHERE (name = N’master’)”” and it returned just master database.

-> Removed the startup parameter -T902 and started the SQL service from cluadmin.msc.

-> SQL server started this time without issues. Script upgrade mode completed fine without any issues and the version changed to appropriate value.

-> Failed over the SQL instance to other node and it came online without issues.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

February 1, 2017

Posted under

Core

Comments

Leave a comment

Server TCP provider failed to listen on [1519]. Tcp port is already in use.

-> I was working on a 2-NODE cluster with physical nodes SERVER_A and SERVER_B. The SQL server instance SERVER_C\IN01 was not coming online in node SERVER_B.

-> SQL server version below,
Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)
Dec 10 2010 10:56:29
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-> I tried failing over SQL server instance from SERVER_B to SERVER_A to check if SQL server comes online on SERVER_A, the SQL services  failed on node SERVER_A as expected.

-> Opened the SQL server error log and found the below,

2016-12-01 23:27:12.73 Server      Server is listening on [ <IP_Address> <ipv4> 1519].
                2016-12-01 23:27:12.74 Server      Error: 26023, Severity: 16, State: 1.
                2016-12-01 23:27:12.74 Server      Server TCP provider failed to listen on [ 10.65.78.24 <ipv4> 1519]. Tcp port is already in use.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2016-12-01 23:27:12.74 Server      Error: 17826, Severity: 18, State: 3.
2016-12-01 23:27:12.74 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2016-12-01 23:27:12.74 Server      Error: 17120, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      SQL Server could not spawn FRunCM thread.

-> It seems that TCP port is in use. Tried running netstats -Aon and found that the port 1519 was not in use.

-> Tried checking the SQL configuration manager for any aliases and found none on SERVER_A.

-> Checked the Configuration Manager -> Network Configuration -> Protocol for the Instance -> TCP/IP -> Properties -> I was able to see port 1519 set for all IP’s and TCP Dynamic Port / TCP port as well.

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and was able to see the same error, PORT IN USE!

-> Tried removing again and started the SQL services and the result was same. When tried refreshing it, the values were present for both Dynamic Port and TCP port. Understood that registry check pointing might cause this.

-> Ran the below command in elevated command prompt to disable registry checkpoint.

cluster res “SQL Server (IN01)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and this time SQL services started.

-> Ran the below command from an elevated command prompt enable registry checkpoint.

cluster res “SQL Server (IN01)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> I failed over the SQL server instance from SERVER_A to SERVER_B to check if SQL comes online on SERVER_B node.

-> The SQL services failed on node SERVER_B.

-> Checked the SQL server error log and did not get much info. Checked the event viewer and was able to understand that SQL services fails while trying for a connection. Suspected this could be due to alias issue.

-> Checked the configuration manager on server SERVER_B and as expected there  was an alias with below details.

Alias name : SERVER_C\IN01
Server name : SERVER_C\IN01
Port : 1476

-> I changed the port number to 1519 in the alias and the SQL services started as expected.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

February 1, 2017

Posted under

Setup

Comments

Leave a comment

The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.

-> We were working on an upgrade project. We were performing an upgrade of a standalone SQL server 2005 to SQL server 2012. The upgrade failed with the error “The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.”.

-> The SQL server version changed to SQL server 2012. We tried a repair and it failed.

-> We tried starting the SQL services and got the below error in the errorlog.

.
.
2016-01-05 20:35:00.25 spid5s      Error: 33009, Severity: 16, State: 2.
2016-01-05 20:35:00.25 spid5s      The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.
2016-01-05 20:35:00.25 spid5s      The failed batch of t-sql statements :
2016-01-05 20:35:00.25 spid5s      Error: 912, Severity: 21, State: 2.
2016-01-05 20:35:00.25 spid5s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 33009, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-05 20:35:00.26 spid5s      Error: 3417, Severity: 21, State: 3.
2016-01-05 20:35:00.26 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-05 20:35:00.26 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

-> I added trace flag -T902 in the SQL server startup parameter and started the SQL services to see if it starts, it started without any issues.

902 Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack.

-> Went into SQL server management studio and ran the below query. “select * from sys.databases”.

-> The owner for master database was SA -> sid: 0x01. But MSDB was something else. Executed the below command and changed the owner of MSDB to SA.

use msdb
go
sp_changeobjectowner ‘sa’
go

-> Once this was done, I removed the trace flag -T902 from the SQL server startup parameter and started the SQL services and it started fine without any issues.

->  Please note that you cannot change the owner of system databases (Master, Model and tempdb) as per link http://technet.microsoft.com/en-us/library/ms190909(v=SQL.105).aspx.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.