Could Not Open SQL Server Errorlog File Errors in Event Viewer of Passive Node

You may sometimes notice errors like below on Passive cluster node.

initerrlog: Could not open error log file 'F:\MSSQL10_50.SQLInst\MSSQL\Log\ERRORLOG'. Operating system error = 3(failed to retrieve text for this error. Reason: 15100).

All SQL Server, IP Address, Network Name, Disk resources will be owned by the Active node, so if you try to start the SQL Services on passive node, it would result in this error.

At times, although no one tries to start the SQL Services on passive cluster node, still you may see this error in the Application Eventlog. This could be result of possibly some application is attempting to start the SQL Server service on passive node.

By running a process monitor(procmon) you should be able to see which process is trying to start the SQL instance on passive node. Some application which may do this are some Virus, Monitoring or Audit software. On one of the occasion we noticed SCCM application which has a service similar to “SMS_SITE_SQL_BACKUP_SCCMXXX” was trying to start the SQL instance on passive node.

Disabling this service on the passive node stopped these errors in our case.

 

SQL Server 2012 SP1 Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 537, State 3, Severity 16

SQL Server 2012 instance does not start after applying SP1. Setup finished successfully, but SQL instance failed to start. Reviewed SQL Errorlog file and noticed below errors at the end of the file where it failed applying the upgrade scripts.

spid8s Error: 537, Severity: 16, State: 3.
spid8s Invalid length parameter passed to the LEFT or SUBSTRING function.
spid8s Error: 912, Severity: 21, State: 2.
spid8s Script level upgrade for database 'master' failed because upgrade step 'msdb110_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.
spid8s Error: 3417, Severity: 21, State: 3.
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.

This error Invalid length parameter passed to the LEFT or SUBSTRING function. will occur if there are any user database logical file names in the instance matches the master or mastlog.

Start SQL Server using -T902 Trace Flag:
To run any commands, we first need to connect to the SQL Server instance, but the SQL Services are in stopped state. We can start the SQL Server by skipping the script upgrade using Trace Flag -T902. Please refer below on how to start SQL Server instance using -T902

How to Start SQL Server Using -T902 Trace Flag to Skip the Script Upgrade

Run below command to find if there are any database logical file names similar to Master database logical file names.

select db_name(database_id) as DatabaseName, name, Physical_name from master.sys.master_files where name like 'mast%'

If above query returns any other user database having logical names are master or mastlog, then either detach that database or rename the logical file names to some other name.

To Detach a Database
Use Master
Go
EXEC SP_Detach_DB @dbname = N'SpecifyDatabaseName'

To Rename Logical File Names
Use Master
Go
ALTER DATABASE MODIFY FILE ( NAME = 'current_logical_name', NEWNAME = 'new_logical_name')

Now start the SQL Server instance by removing Trace Flag -T902. Check SQL Errorlog to ensure the upgrade scripts completed successfully.

Cheers,
Keerthi Deep

 

Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade

Whenever we install a patch(Service Pack or Cumulative Update) to SQL Server or if we upgrade SQL Server from one version to another version(Ex: SQL Server 2008 to SQL Server 2012), at the end of the up-gradation process some scripts are applied to Master and MSDB databases. Sometimes, there are possibilities that the script upgrade may fail due to various reasons like below.

Script level upgrade for database ‘master’ failed after Applying SQL 2008 R2 SP2 due to Error “The database principal owns a schema in the database, and cannot be dropped”

Script Upgrade Failure to Apply msdb110_upgrade Sript

At that time we would have to start SQL Server instance using -T902 to skip SQL Server from attempting to apply the scripts, so that we can take corrective actions to fix the problem causing failure of the scripts and finally remove Trace Flag -T902 and start SQL Server normally which will then successfully apply the upgrade scripts.

Below are the steps to perform to start SQL Server to skip the script upgrade using Trace Flag -T902

1. Start -> All Programs -> Microsoft SQL Server 2008 R2 or the highest version installed -> Configuration Tools -> SQL Server Configuration Manager

2. In SQL Server Configuration Manager, click SQL Server Services.

3. In the right pane, right-click SQL Server (), and then click Properties.

4. On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T902), and then click Add. You will now see the parameters similar to below

SQL Server Istance Startup Parameters

SQL Server Istance Startup Parameters


5. Click OK.
6. Restart the Database Engine.
7. Connect to SQL Server instance from SQL Server Management Studio and take corrective actions to resolve the errors causing script upgrade to fail.
8. Now finally remove the -T902 trace flag from SQL Server Configuration manager
9. Restart SQL Server Instance
10. Verify Errorlog to make sure script upgrade finished successfully

 

Script level upgrade for database ‘master’ failed after Applying SQL 2008 R2 SP2

I worked on a issue where SQL Server 2008 R2 instance failed to start after installing service pack 2. SP2 installation was successful, but SQL Services fail to come online. Checked from services.msc and found SQL Services are in stopped state and SQL cluster resources were in offline state.

Checked SQL errorlog and found below error caused the failure

spid8s Creating procedure [dbo].[sp_syspolicy_purge_health_state] ...
spid8s Error: 15138, Severity: 16, State: 1.
spid8s The database principal owns a schema in the database, and cannot be dropped.
spid8s Error: 912, Severity: 21, State: 2.
spid8s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15138, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be tak
spid8s Error: 3417, Severity: 21, State: 3.
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.


Error “The database principal owns a schema in the database, and cannot be dropped” will occur if we try to drop a user that owns a schema. In this case, script upgrade is running scripts from file sqlagent100_msdb_upgrade.sql where it was trying to drop a user. Reviewed the script “sqlagent100_msdb_upgrade.sql” which is found in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install”

From the script, found below code where it was trying to drop user ##MS_PolicyEventProcessingLogin## and later recreating it.

IF EXISTS (SELECT * from sys.database_principals where name = N'##MS_PolicyEventProcessingLogin##')
DROP USER [##MS_PolicyEventProcessingLogin##]
GO
use master
GO
IF EXISTS (SELECT * from sys.server_principals WHERE name = '##MS_PolicyEventProcessingLogin##')
BEGIN
IF EXISTS (SELECT * from sys.server_triggers WHERE name = N'syspolicy_server_trigger')
DROP TRIGGER [syspolicy_server_trigger] ON ALL SERVER
DROP LOGIN [##MS_PolicyEventProcessingLogin##]
END

SQL was unable to drop the user “MS_PolicyEventProcessingLogin” because this user was owning schema “db_owner” in MSDB database. To allow dropping this user we need to transfer the ownership to another user.

First, to run any SQL commands, we need to establish the connection to SQL Server, but in this case the SQL instance is in stopped state.

Using below steps started SQL Server using trace flag -T902 where it skips running the upgrade scripts.
SQL Server Configuration Manager can be used to set the SQL Server start-up parameters. You can specify the trace flag there:

Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

In SQL Server Configuration Manager, click SQL Server Services.

In the right pane, right-click SQL Server (), and then click Properties.

On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T902), and then click Add. You will now see the parameters similar to below

SQL Server Istance Startup Parameters

SQL Server Istance Startup Parameters

Click OK.
Restart the Database Engine.

Ran below command to transfer the ownership of schema db_owner to user dbo
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo

Removed trace flag -T902 and started SQL Server normally and this time the SQL Server completed running the upgrade scripts successfully.

Tested the failover and failback and everything worked well.

Cheers,
Keerthi Deep

 

How to add a new instance to a 2 node SQL Server Cluster under the same networkname

I have got this question in one of the forums where a novice user was trying perform SQL Server installation. He had a question where he installed a SQL Server instance as cluster aware with a network name, ex: SQLServerClus. Now he was trying to add another SQL Server instance as a cluster aware and wanted to use the same network name SQLServerClus.

This is not possible. We cannot use the same network name for two SQL instances. We need to use a different network name for the second instance.

A failover cluster instance contains:
– A combination of one or more disks in a Microsoft Cluster Service (MSCS) cluster group, also known as a resource group. Each resource group can contain at most one instance of SQL Server.
– A network name for the failover cluster instance.
– One or more IP addresses assigned to the failover cluster instance.
– One instance of SQL Server that includes SQL Server, SQL Server Agent, the Full-text Search (FTS) service, and Replication. You can install a failover cluster with SQL Server only, Analysis Services only, or SQL Server and Analysis Services

http://msdn.microsoft.com/en-in/library/ms179410(v=sql.105).aspx

 

Error while Editing the backup maintenance plan in SQL Server 2005

Recently while I was trying to edit a maintenance plan, when trying to edit the backup task using Management Studio resulted in an error. Below is the error message


TITLE: Microsoft SQL Server Management Studio
Cannot show the editor for this task.
Value of ’27/6/2014 12:00:00 AM’ is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.

After some research, found that this has impacted other users as well and a connect bug Link has been filed for the same.

There was no fix released for this, but there is a work around available which resolved the issue and then I was able to edit the backup task in the maintenance plan.

Below is the work around

1. Modify Maintenance Plan.
2. Click On the backup task.
3. Click F4 button on the keyboard and you will find the properties window to the right corner of the SSMS.
4. Change the value in “expiredate” by adding 10 years or Clear out the value in “expiredate”

Backup Task Maintenance Plan Properties

Backup Task Maintenance Plan Properties


5. Save the maintenance plan.
6. Reopen the maintenance plan and now you should be able to edit the backup task.

Happy Reading,
SQLServerF1 Team

 
1 556 557 558