2008 SP1 update error

Question

Hi All,

I have run into an issue while installing 2008 SP1. Here is the background info:

 

Upgraded SQL 2000 SP4 Enterprise  to 2008 Enterprise. System is a publisher/distributor in a merge replication schema.

Upgrade went ok, replication is working. Replication Monitor is not working due to a known bug (which is resolved by SP1 and a hotfix patch).

Anyhow after SP1 installation server fails to start due to a corrupted master database. The below log should show why the master gets corrupted:

 

 

2010-10-25 20:35:54.27 spid7s   Database 'master' is upgrading script 'msdb_upgrade_discovery.sql' from level 167773760 to level 167774691.
2010-10-25 20:35:54.27 spid7s    
2010-10-25 20:35:54.27 spid7s   ----------------------------------------------------------------
2010-10-25 20:35:54.27 spid7s   msdb_upgrade_discovery starting
2010-10-25 20:35:54.91 spid7s   MSDB format is: SQL Server 2008
2010-10-25 20:35:54.98 spid7s   User 'sa' is changing database script level entry 4 to a value of 2.
2010-10-25 20:35:54.98 spid7s   User 'sa' is changing database script level entry 5 to a value of 2.
2010-10-25 20:35:54.98 spid7s   User 'sa' is changing database script level entry 6 to a value of 2.
2010-10-25 20:35:54.98 spid7s   User 'sa' is changing database script level entry 6 to a value of 0.
2010-10-25 20:35:54.98 spid7s   Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script
2010-10-25 20:35:54.98 spid7s   ----------------------------------------------------------------
2010-10-25 20:35:54.98 spid7s    
2010-10-25 20:35:55.76 spid7s   Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 2.
2010-10-25 20:35:55.76 spid7s   ----------------------------------------
2010-10-25 20:35:55.76 spid7s   Starting execution of PREINSTMSDB100.SQL
2010-10-25 20:35:55.76 spid7s   ----------------------------------------
2010-10-25 20:35:56.03 spid7s   Error: 15002, Severity: 16, State: 1.
2010-10-25 20:35:56.03 spid7s   The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction.
2010-10-25 20:35:56.07 spid7s   Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-10-25 20:35:56.07 spid7s   Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-10-25 20:35:56.07 spid7s   Error: 574, Severity: 16, State: 0.
2010-10-25 20:35:56.07 spid7s   CONFIG statement cannot be used inside a user transaction.
2010-10-25 20:35:56.07 spid7s   Error: 912, Severity: 21, State: 2.
2010-10-25 20:35:56.07 spid7s   Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 574, state 0, 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.
2010-10-25 20:35:56.08 spid7s   Error: 3417, Severity: 21, State: 3.
2010-10-25 20:35:56.08 spid7s   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-10-25 20:35:56.08 spid7s   SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

 

Uninstalling SP1 "fixes" the issue and the server starts-up ok. I looked at the 'sqlagent100_msdb_upgrade.sql' script ad it looks like it fails right at the beginning. The question is why, and how can I install SP1 and 'http://support.microsoft.com/kb/970150' hotfix?

 

Thanks

 

Raf

 

Answer 1

Hi Raf,

 

Based on my research, this issue might be caused by “implicit transactions” of this server is set by default. If this option is set, SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. However as said in the error message, the sys.sp_dbcmptlevel cannot be executed within a transaction. Therefore this issue occurs.

 

In order to solve this issue, I would like to recommend that you check if this option is set. If it is set, please disable it temporarily.

In order to check if it is set, please follow the steps below:

1.       Connect to this instance

2.       Right-click on “<instance>” in Object Explorer and choose Properties

3.       In Server Properties dialog, please switch to Connections page

4.       Check if “implicit transactions” is checked in the textbox area
if it is checked, please uncheck it and click OK button.

 

If anything is unclear, please let me know.

---

Regards,
Tom Li

Answer 2

It worked. Thanks for the help.

 

R.

Answer 3

Hello, I have exactly the same error by upgrading from SQL 2005 to 2008 but I can not set up the implicit transaction mode via Management Studio, but I can connect in admin mode to the master DB. Is user options = '0' correct for implicit transactions = OFF? If yes, this dond't solve the problem. The Problem is like this: https://connect.microsoft.com/SQLServer/feedback/details/610217/sqlserver-2008-sp2-installation-failed#tabs