JJKusch


Have an interesting situation.

I have a NeverFail cluster on which I loaded SQL Server 2005 and SP1.

All was great. Both nodes had no issues with the initial setup or the SP1 update.

When I forced a failover to the inactive node, MOST of the services like SSIS, Full-Text and SQL Browser came up like a champ.

BUT ... the SQL Server service and the SQL Agent did not come up (or shall I say not fully).

I am now looking at the SQL Server Configuration Manager and I see the following for the SQL Server service:

"Name" shows the RED block icon for the SQL Server

"State" shows "Change pending ..."

"Start Mode" is set to Manual

"Log on as" has my domain account listed

"Process ID" has the number 2956 (so it has started somewhat).

I then click open the SQL Service item and it shows that it is stopped.

I am given the option to START the service. When I try to start it, the meter bar comes up and moves slowly to the end and then an error returns of ...

"The request failed or the service did not respond in a timely manner. Consult the event log or other applicable errors logs for details"

If I look under windows Services in the Admin Tools section... I see the services for SSIS, FT and Browser listed and started BUT I see SQL Server says its "starting".

If I click open the service from here, once again it show "starting" and all of the option buttons to Stop-Start-Pause-Resume are grayed out and I am not able to use them.

I have looked in the Windows Event Logs for any events but none are present.

Now I know why the SQL Agent is not running, and of course that is due to is dependency on SQL Server being up and running.

First off WHAT does the "Change pending" message mean

Then what other logs can I look at for some help or is there someone who has the answers to this dilemma.

Thanks and have a great evening




Re: SQL Server 2005 service hung ... "Change Pending"

Hemantgiri S. Goswami


Hi,

service start mode should be AUTOMATIC.

Hemantgiri S. Goswami







Re: SQL Server 2005 service hung ... "Change Pending"

JJKusch

Evening Hemantfiri,

Actually in this case all of the SQL Server services, that being SSIS, Full-Text, SQL Browers, SQL Server and the SQL Server Agent, have to be set to manual since we are using a 3rd party replication/clustering software called NeverFail.

When a failover occurs, the NeverFail application executes a bat file to stop the needed services on the box before it passes control over to the other server.

Once the other server takes over, NeverFail again fires a start bat file to start the needed services.

SO a NeverFail cluster cannot have service like SQL Server to start automatically.

Thanks for the info though!






Re: SQL Server 2005 service hung ... "Change Pending"

jonescDC

Hi JJ,

I was wondering if you ever found a resolution to this issue I am having a very similar issue with a stand-alone SQL server 2005 installation. Upon initially booting my DB server, the SQL Server service is going into the "Change Pending..." state and hanging. If I kill the service and restart it, everything works as expected. Any thoughts

Thanks,

Chris





Re: SQL Server 2005 service hung ... "Change Pending"

tschi2001

Hello Chris,

Can you explain me please how I can kill a service

I tried to work with services (Strart->Settings->Controll Panel->Administrative Tools-> Services).

To restart the Analysis Service doní»t resolve my problem.

Kind regards,

Alex





Re: SQL Server 2005 service hung ... "Change Pending"

tschi2001

Okay by fluke I founded the possibility to restart the services at computer management (Start->Settings->Controll Panel->Administrative Tools->computer management) now analysis services work without any problem

Alex




Re: SQL Server 2005 service hung ... "Change Pending"

tschi2001

Sorry that I have to write again, but I doní»t resolve this problem like I thought. After reset my system the error came back.

In Event Viewer a System is a following message:

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

Event Type: Error

Event Source: Service Control Manager

Event Category: None

Event ID: 7022

Date: 11/1/2007

Time: 12:55:05 PM

User: N/A

Computer: MyComputer

Description:

The SQL Server Analysis Services (MSSQLSERVER) service hung on starting.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

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

Have somebody other ideas to resolve this issue

Best regards,

Alex





Re: SQL Server 2005 service hung ... "Change Pending"

Ajmer Dhariwal

The event log won't give us enough information.

Can you post the full SQL error log for this instance





Re: SQL Server 2005 service hung ... "Change Pending"

tschi2001

Hello Ajmer,

After restart my system the log files have this content:

C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log:

(11/2/2007 8:14:31 AM) Message: The flight recorder was started. (Source: \\ \C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)

(11/2/2007 8:14:31 AM) Message: Service started. (Source: \\ \C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG:

2007-11-02 08:10:20.97 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2007-11-02 08:10:20.97 Server Detected 1 CPUs. This is an informational message; no user action is required.

2007-11-02 08:10:21.25 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2007-11-02 08:10:21.40 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

2007-11-02 08:10:22.94 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

2007-11-02 08:10:22.94 Server Database mirroring has been enabled on this instance of SQL Server.

2007-11-02 08:10:23.16 spid5s Starting up database 'master'.

2007-11-02 08:10:24.29 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2007-11-02 08:10:24.64 spid5s SQL Trace ID 1 was started by login "sa".

2007-11-02 08:10:24.94 spid5s Starting up database 'mssqlsystemresource'.

2007-11-02 08:10:25.02 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.

2007-11-02 08:10:25.24 spid5s Server name is 'MRWLAP97_PPS'. This is an informational message only. No user action is required.

2007-11-02 08:10:25.69 Server A self-generated certificate was successfully loaded for encryption.

2007-11-02 08:10:25.72 Server Server is listening on [ 'any' <ipv4> 1433].

2007-11-02 08:10:25.72 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].

2007-11-02 08:10:25.72 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].

2007-11-02 08:10:25.74 spid9s Starting up database 'model'.

2007-11-02 08:10:25.77 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].

2007-11-02 08:10:25.77 Server Dedicated admin connection support was established for listening locally on port 1434.

2007-11-02 08:10:25.83 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

2007-11-02 08:10:25.83 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

2007-11-02 08:10:26.58 spid12s Starting up database 'msdb'.

2007-11-02 08:10:26.59 Logon Error: 18456, Severity: 14, State: 16.

2007-11-02 08:10:26.59 Logon Login failed for user 'MySystem\Administrator'. [CLIENT: <local machine>]

2007-11-02 08:10:26.63 spid13s Starting up database 'MyDatabase1'.

2007-11-02 08:10:27.42 Logon Error: 18456, Severity: 14, State: 16.

2007-11-02 08:10:27.42 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

2007-11-02 08:10:28.56 spid9s Clearing tempdb database.

2007-11-02 08:10:29.08 spid13s 10 transactions rolled forward in database 'SharePoint_Config' (5). This is an informational message only. No user action is required.

2007-11-02 08:10:29.67 spid5s 0 transactions rolled back in database 'SharePoint_Config' (5). This is an informational message only. No user action is required.

2007-11-02 08:10:29.67 spid5s Recovery is writing a checkpoint in database 'SharePoint_Config' (5). This is an informational message only. No user action is required.

2007-11-02 08:10:29.91 spid13s Starting up database 'MyDatabase2'.

2007-11-02 08:10:32.21 spid5s Recovery is writing a checkpoint in database 'PPSPlanningService' (8). This is an informational message only. No user action is required.

2007-11-02 08:10:32.66 spid5s Recovery is writing a checkpoint in database 'SharedServiceProvider_Content' (9). This is an informational message only. No user action is required.

2007-11-02 08:10:32.75 spid13s Starting up database 'MyDatabase3'.

2007-11-02 08:10:34.32 spid14s 3 transactions rolled forward in database 'SharedServices1_DB' (11). This is an informational message only. No user action is required.

2007-11-02 08:10:35.59 spid5s 0 transactions rolled back in database 'SharedServices1_DB' (11). This is an informational message only. No user action is required.

2007-11-02 08:10:35.59 spid5s Recovery is writing a checkpoint in database 'SharedServices1_DB' (11). This is an informational message only. No user action is required.

2007-11-02 08:10:37.47 Logon Error: 18456, Severity: 14, State: 16.

2007-11-02 08:10:37.47 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

2007-11-02 08:10:37.57 spid5s Recovery is writing a checkpoint in database 'PPS_Content' (13). This is an informational message only. No user action is required.

2007-11-02 08:10:40.10 spid5s Recovery is writing a checkpoint in database 'WSS_Search_MRWLAP97_PPS' (10). This is an informational message only. No user action is required.

2007-11-02 08:10:44.62 spid16s The Service Broker protocol transport is disabled or not configured.

2007-11-02 08:10:44.62 spid16s The Database Mirroring protocol transport is disabled or not configured.

2007-11-02 08:10:45.26 spid16s Service Broker manager has started.

2007-11-02 08:10:45.63 spid5s Recovery is writing a checkpoint in database 'Test1' (21). This is an informational message only. No user action is required.

2007-11-02 08:10:46.64 spid5s Recovery is writing a checkpoint in database 'MinimalVerification_AppDB' (23). This is an informational message only. No user action is required.

2007-11-02 08:10:49.12 spid5s Recovery is writing a checkpoint in database 'Alpine_Ski_House_AppDB' (22). This is an informational message only. No user action is required.

2007-11-02 08:10:51.70 spid5s Recovery is writing a checkpoint in database 'Test' (27). This is an informational message only. No user action is required.

2007-11-02 08:11:22.99 spid12s Analysis of database MyDatabase4' (29) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.

2007-11-02 08:11:35.74 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.

2007-11-02 08:11:35.74 spid5s Recovery is complete. This is an informational message only. No user action is required.

2007-11-02 08:11:43.70 spid54 Using 'xpsqlbot.dll' version '2005.90.3042' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.

2007-11-02 08:11:47.68 spid54 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

2007-11-02 08:11:49.06 spid54 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.

I caní»t see any relevant information I hope you can.

I looked for dependencies for this service but there are no dependencies.

Best regards,

Alex





Re: SQL Server 2005 service hung ... "Change Pending"

Ajmer Dhariwal

You've got the 'Recovery is complete' messagein the SQL error log and Analysis Service's flight recorder is running, so as far as SQL is concerned, everything's up and running.

Do you get any errors when you try to connect to SQL





Re: SQL Server 2005 service hung ... "Change Pending"

tschi2001

No, I didn't get any errors when I worked with SQL but it needs a very long time. If I try to deploy a Business Intelligence project in Visual Studio the system will hung and I have to restart the system.

I fear that I have not enough memory. After I reset my system sometimes analysis engine worked without any problem but other engines didní»t work. After I looked up the task manager I saw that there are just 10 MB of 1200 MB available.

Thanks,

Alex