Unable to connect to SQL Server immediately after applying Service Pack /Cumulative updates.
Symptoms.
========
You are unable to connect to SQL Server immediately after applying Service Pack or any Cumulative updates. You will see the SQL Server in Started status for few seconds and then it would stop.
On a cluster environment you will see the SQL Server resource online for few seconds and then it would go Offline and eventually failover to the other node
During this period if you try to connect to the instance using Management Studio you may receive the below error
"Login failed for user 'Server\Administrator'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: <local machine>]
Observations:
If you look at the SQL Errorlog you will see that the Server is getting ready for databases upgrade
Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 2.
Scrolling down to the bottom of the errorlog reveals the actual error.
-------------------ErrorLog------------------------
2010-04-03 03:27:04.29 spid6s ------------------------------------
2010-04-03 03:27:04.29 spid6s Moving 2005 SSIS Data to 2008 tables
2010-04-03 03:27:04.29 spid6s ------------------------------------
2010-04-03 03:27:04.29 spid6s Mapping SSIS yukon roles to katmai roles...
2010-04-03 03:27:04.31 spid6s Moving package folders...
2010-04-03 03:27:04.32 spid6s Moving packages...
2010-04-03 03:27:04.34 spid6s Error: 207, Severity: 16, State: 1.
2010-04-03 03:27:04.34 spid6s Invalid column name 'packagedata'.
2010-04-03 03:27:04.40 spid6s Error: 912, Severity: 21, State: 2.
2010-04-03 03:27:04.40 spid6s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25. 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.
2010-04-03 03:27:04.44 spid6s Error: 3417, Severity: 21, State: 3.
2010-04-03 03:27:04.44 spid6s 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.
2010-04-03 03:27:04.45 spid6s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
-------------------/ErrorLog------------------------
-------------------Setuplogs-----------------------
You will see the installation/upgrade reporting success in the Setup logs
File: sql_engine_core_inst_Cpu32_1
MSI (s) (58:B4) [03:17:22:484]: Product: Microsoft SQL Server 2008 Database Engine Services -- Configuration completed successfully.
MSI (s) (58:B4) [03:17:22:484]: Windows Installer reconfigured the product. Product Name: Microsoft SQL Server 2008 Database Engine Services. Product Version: 10.1.2531.0. Product Language: 1033. Reconfiguration success or error status: 0.
-------------------/Setuplogs-----------------------
Observations continued:
The error Invalid column name 'packagedata' is preventing the database upgrade.
Let’s look into the 'sqlagent100_msdb_upgrade.sql for more details.
You will find this file under the installation upgrade folder for eg: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Upgrade\
Open this file and find the string "Moving packages". Here you will see the script trying to move 2005 SSIS Data to 2008 tables
--------------Sample excerpt from the script:------------
IF EXISTS (select * from sys.objects where object_id = object_id(N'[dbo].[sysdtspackages90]') AND type = N'U')
INSERT INTO [msdb].[dbo].[sysssispackages]
([name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid])
SELECT [name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysdtspackages90]
GO
--------------/Sample excerpt from the script:------------
Its very likely that the above snippet is failing. To verify this you may have to attach your MSDB database to another SQL Server as a User database and run this query. You should get the error
Error: 207, Severity: 16, State: 1.
Invalid column name 'packagedata'.
Running a Select on the [msdb].[dbo].[sysdtspackages90] will confirm that the column 'packagedata' does not exist which is the root cause for our failure.
Running a Select * from Sysobjects where name ='sysdtspackages90' will reveal that this object is a View. But as per BOL this should be a table https://msdn.microsoft.com/en-us/library/ms181582(SQL.90).aspx. This Contains one row for each package that is saved to Microsoft SQL Server 2005. This table is stored in the msdb database
PostMortem:
Run "Select * from sysobjects order by crdate desc". Check the date when this view was created. Look for other objects created on the same day. This should give you an idea who created this object(Not a straight forward hint ;)). Check for any application installed during the same day. Additionally you could also get a hint as to which application\user created this view by looking at the default SQL traces. You will find these files under the same path where your SQL Errorlogs resides. These traces contain the Object:Created, Object:Altered and Object Deleted events. Check for events at the same duration that of the Crdate of the view. If the SQL Server has been restarted several times after the view has been created then it’s less likely that you will have these events as new trace files are created at every restart and SQL maintains only last 5 traces by default.
Usually third party applications which are 2005 compatible would make a quick fix by creating views in SQL2008 for backward compatibility. Since not all SQL 2005 object names exist in SQL 2008, instead of making code changes in the application the easier method is to create views with the same name as SQL 2005 tables.
If you review the text of the view sysdtspackages90 by running "sp_helptext sysdtspackages90"
You will see that the view is refering to the [sysssispackages] table and the packagedata column being commented which causes the Insert statement in the Upgrade script to fail
SELECT [name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
--,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysssispackages]
WorkAround
----------------
Method 1: Restore the Master and msdb databases with the latest backup
Rename the "sysdtspackages90" view
Apply the ServicePack or the Latest Cumulative Update
Method 2: Attach the current problematic Msdb to another SQL Instance as a user database.
Rename the "sysdtspackages90" view
Dettach the database and replace the files on the Original Sever
Start the SQL Service. Allow the database upgrade to complete
Method 3: Start the SQL Services from the Services Console or Cluster Administrator. You may have to perform the below steps quicker since the SQL Services would stay live only for few ticks but would give you enough breath to perform the below steps.
Make a DAC connection as per https://msdn.microsoft.com/en-us/library/ms189595.aspx
Run the below command to rename the Sysdtspackages90 view
Use Msdb
Go
EXEC sp_rename N'sysdtspackages90', N'Renamed_Sysdtspackages90'
You may wait for the SQL Services to finish the Upgrade. Confirm this from the SQL Errorlog
Levi Justus
Technical Lead, Microsoft SQL Server.