Skip to main content

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql'

As huge a database grows, that tedious are regular DBA activities become. Besides, a warehouse database, can grow with no bounds to challenge. 

There was a patching on one of our databases, SP3 for SQL Server 2008 R2, which was failing due to the error below. As the error says, MASTER the heart of SQL Server 

“Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
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.”

The Error 2627, State 1, severity 14 leads us to Primary Key Violation.

So, we decided to find where exactly the error is being thrown. The sqlagent100_msdb_upgrade.sql script is found at the location:
 "C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Upgrade". 

After a Service Pack upgrade, the very next SQL Service start would run a set of .sql scripts, which is when it fails. 

So, per our research we tried this work around as given in the KB Article :

1.       Enable trace flag 902 on the instance of SQL Server 2008 R2. To do this, follow these steps:
a.       Open SQL Server Configuration Manager.
b.       In SQL Server Configuration Manager, click SQL Server Services.
c.        Double-click the SQL Serverservice.
d.       In the SQL Server Properties dialog box, click the Advanced tab.
e.       On click the Advanced tab, locate the Startup Parameters item.
f.         Add ;-T902 to the end of the existing string value, and then click OK.
2.       Right-click the SQL Serverservice, and then click Start.
3.       If the SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop.
4.       Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.
5.       Run the following statements:
EXEC sp_configure 'show advanced', 1;
RECONFIGURE;
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE;
GO
6.       In SQL Server Configuration Manager, right-click the SQL Serverservice, and then click Stop.
7.       Remove trace flag 902 on the instance of SQL Server 2008 R2. To do this, delete ;-T902 from the string value that you updated in step 1f.
8.       Right-click the SQL Serverservice, and then click Start.
9.       Right-click the SQL Server Agent service, and then click Start.

No use, soon after the trace flag is removed, we still get the error. T902 is just to disable the running of upgrade scripts. In the SQL Error Logs, we could get hold of the table where we got the primary key violation, which is sysssispackages.

The error was because, there was meta data for a few 2005 SSIS packages in the table sysdtspackages90, which was being moved to the table sysssispackages, which already had that meta data. Hence, the primary key violation. 

Resolution: Truncate the table sysdtspackages90, if you feel it’s no more needed, or all 2005 packages are upgraded, and restart the services without Trace Flag T902.

We found that the backward compatibility table sysdtspackages90 is no more existing on the SP3.
Note: 1. Take a backup of the table sysdtspackages90 before you take an action on this.
2. When the .sql scripts are being applied during service startup, all databases need to be in consistent state, even User Databases. So wait until all the databases are recovered to FULLEST. Else, you would see an error while logging in, saying Server is in script upgrade mode.


Labels:

Comments

Post a Comment

Popular posts from this blog

Digging into a long running job

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...
Post a Comment
Read more

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...
Post a Comment
Read more