Unable to connect to SQL Server 2008 instance after applying SP1

Problem Description

===============

Not able to connect to SQL Server 2008 instance after applying SP1.

Connection would fail with the following error

TITLE: Connect to Server

------------------------------

Cannot connect to <Server name>

------------------------------

ADDITIONAL INFORMATION:

Login failed for user '<Domain Name>\<user name>'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18401&LinkId=20476

------------------------------

BUTTONS:

OK

Cause

=====

Sqlagent100_msdb_upgrade.sql from SQL Server 2008 SP1 upgrade did not complete successfully. Following errors were reported in the ERRORLOG.txt

2010-03-26 20:09:45.33 spid7s Error: 17750, Severity: 16, State: 0.

2010-03-26 20:09:45.33 spid7s Could not load the DLL xpstar.dll, or one of the DLLs it references. Reason: 193(%1 is not a valid Win32 application.).

2010-03-26 20:09:45.33 spid7s Uploading data collector package from disk: Install\SqlTraceCollect.dtsx

2010-03-26 20:09:45.34 spid7s Uploading data collector package from disk: Install\SqlTraceCollect.dtsx

2010-03-26 20:09:45.34 spid7s Error: 4861, Severity: 16, State: 1.

2010-03-26 20:09:45.34 spid7s Cannot bulk load because the file "Install\SqlTraceCollect.dtsx" could not be opened. Operating system error code 3(The system cannot find the path specified.).

2010-03-26 20:09:45.34 spid7s Error: 912, Severity: 21, State: 2.

2010-03-26 20:09:45.34 spid7s 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.

Resolution

========

1) To begin with ensure that XPStar.dll, SQLTraceCollect.dtsx is located under -

<OS DRIVE>:\Program Files\Microsoft SQL Server\MSSQL10.<INSTANCENAME>\MSSQL\Install

2) If these files exist, then open xpstar.dll with dependency walker

https://www.dependencywalker.com/

3) Check for missing dependencies for xpstar.dll

4) In our case dependency walker confirmed that ATL80.dll under

C:\Windows\WinSxS\amd64_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_8 a17faaf2edd3e00\ was failing to load.

5) Downloaded the latest “Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package

ATL Security Update from “https://www.microsoft.com/downloads/details.aspx?familyid=766a6af7-ec73-40ff-b072-9112bab119c2&displaylang=en

6) Restarted SQL server services and the upgrade scripts could run this time.

Kumar Bijayanta
SE, Microsoft SQL Server.

Reviewed by
Shamik Ghosh & Amit banerjee
Technical Lead, Microsoft SQL Server.