SQL 2005 SP 2 Upgrade --- FAILED AT SP 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2005 SP 2 Upgrade --- FAILED AT SP 2

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-06-28 : 13:13:18

Folks:

I upgraded (in-place) our SQL 2000 (SP4) server to SQL 2005 SP 2 today. The initial upgrade went fine but when I applied SP 2 it showed failure for Database Services. Below is the error. But when I logged into SQL Server it shows me version as 9.0.3042 ... So I am not sure whether it really applied SP 2 or not? I googled and found that the mssqlsystemresources files (mdf,ldf) and system databases (master) data/log files are on differenct path. In my case it is not.

Property(S): CARemoveFTEServiceDependency.68C6D15C_77E0_11D5_8528_00C04F68155C = msftesql
Property(S): SqlInstance.76B6DC80_78FC_4336_A8CC_7DE3A2C8CFD8 = D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLProperty(S): UpgradeInstruction = Connect to SQL instance SBACHSQL3 as sysadmin
Launch SQL statement USE master
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\sysdbupg.sql
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\repl_hotfix_install.sql
Stop service MSSQLSERVER
Property(S): SqlUpgradeMessage = SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.. To continue, correct the problem, and then run SQL Server Setup again.
Property(S): SQLBROWSERACCOUNT = SBA\SQLAdmin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 13:58:43

http://support.microsoft.com/kb/918695

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-06-28 : 14:00:18

I saw that KB Article earlier but it is for SP1 and I am installing SP2. Also, I have everything on the same location.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-28 : 14:38:30

SQL Server 2005 9.0.3042 means SP2 has already been applied.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-28 : 14:53:50

Looks like a component failed to install. Is the error for Database engine or any service? (Integration Service or Notification Service?)

Don't sit back because of failure. It will come back to check if you still available. -- Binu

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-28 : 15:00:42

quote:
Originally posted by sqldba20

Folks:

I upgraded (in-place) our SQL 2000 (SP4) server to SQL 2005 SP 2 today. The initial upgrade went fine but when I applied SP 2 it showed failure for Database Services. Below is the error. But when I logged into SQL Server it shows me version as 9.0.3042 ... So I am not sure whether it really applied SP 2 or not? I googled and found that the mssqlsystemresources files (mdf,ldf) and system databases (master) data/log files are on differenct path. In my case it is not.

Property(S): CARemoveFTEServiceDependency.68C6D15C_77E0_11D5_8528_00C04F68155C = msftesql
Property(S): SqlInstance.76B6DC80_78FC_4336_A8CC_7DE3A2C8CFD8 = D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLProperty(S): UpgradeInstruction = Connect to SQL instance SBACHSQL3 as sysadmin
Launch SQL statement USE master
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\sysdbupg.sql
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql
Launch script file D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\repl_hotfix_install.sql
Stop service MSSQLSERVER
Property(S): SqlUpgradeMessage = SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.. To continue, correct the problem, and then run SQL Server Setup again.
Property(S): SQLBROWSERACCOUNT = SBA\SQLAdmin

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-28 : 21:21:02

If the Database Services Failed, check your permissions, when you are trying to install the the Service Pack. Try Logging in as the Administrator for installation(ON the SERVER). Also check your permission on the Data Folders.(Add the Administrator to the Data Folder as well).
I Got this Same errors While Doing the Cumulative Updates.
Work around permissions and you wil be ok.

Maninder

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-07-01 : 10:33:28

Folks:

Thought Of giving an update. I fixed this problem. Even though it showed when I did @@version as 9.0.3042... SP 2 was not applied on Database Services.

How do I confirm this?
I knew of a system stored procedure 'sp_refreshsqlmodule' which was introduced in SQL 2005 SP2 and not in SP1 or earlier.

What did I do to fix the problem?
Using Windows 'ProcessExplorer' I was able to find exactly where the myssqlsystemresource.mdf and ldf files are located and where the master device is located. Accordingly changed my master database mdf and ldf files to be on the same drive as myssqlsystemresource.mdf and ldf and then applied SP2 again and it went pretty smooth.

In short follow the same instructions given in the link below even though it says it applied only for SP1.

http://support.microsoft.com/kb/918695

Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources