April 17, 2009 at 8:25 am
We have a SQL 2005 failover cluster setup that has been running for a few weeks now. On Tuesday of this week we took the databases live on it. It has been working great until this morning at 4:30am. Ever since then the mssqlservice has stopped every hour on the hour. We go in and manually restart the service and it comes right back up.
HELP, this is now a production box and the natives are restless
April 17, 2009 at 10:21 am
In the SQL Error log does it give you any further information? Or the Windows System log that should give an indication of who stopped the service.
April 17, 2009 at 10:39 am
As Nick said check the logs; but few things that might be causing the services to startup are communication failure. When the services die do you see that the resources have bounced over from one server to another? It might indicate that you resources are not coming online in proper order so you might have dependence issues. Where the proper resource is not online and the SQL Server is trying to start up and failing.
If you can paste errors we might be able help other ways..
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 17, 2009 at 1:13 pm
2009-04-17 04:39:57.51 Server Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)
2009-04-17 04:39:57.51 Server (c) 2005 Microsoft Corporation.
2009-04-17 04:39:57.51 Server All rights reserved.
2009-04-17 04:39:57.51 Server Server process ID is 2900.
2009-04-17 04:39:57.51 Server Authentication mode is MIXED.
2009-04-17 04:39:57.51 Server Logging SQL Server messages in file 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-04-17 04:39:57.51 Server This instance of SQL Server last reported using a process ID of 3264 at 4/17/2009 4:33:36 AM (local) 4/17/2009 8:33:36 AM (UTC). This is an informational message only; no user action is required.
2009-04-17 04:39:57.51 Server Registry startup parameters:
2009-04-17 04:39:57.51 Server -d S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-04-17 04:39:57.51 Server -e S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-04-17 04:39:57.51 Server -l S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-04-17 04:40:00.55 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-04-17 04:40:00.55 Server Detected 2 CPUs. This is an informational message; no user action is required.
2009-04-17 04:40:00.59 Server Error: 8313, Severity: 16, State: 1.
2009-04-17 04:40:00.59 Server Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.
2009-04-17 04:40:00.59 Server Error: 3409, Severity: 16, State: 1.
2009-04-17 04:40:00.59 Server Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
2009-04-17 04:40:00.62 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.
2009-04-17 04:40:00.65 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-04-17 04:40:02.84 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.
2009-04-17 04:40:02.84 Server Database mirroring has been enabled on this instance of SQL Server.
2009-04-17 04:40:02.85 spid5s Starting up database 'master'.
2009-04-17 04:40:03.01 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-04-17 04:40:03.05 spid5s CHECKDB for database 'master' finished without errors on 2009-04-17 01:00:01.413 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.09 spid5s SQL Trace ID 1 was started by login "sa".
2009-04-17 04:40:03.11 spid5s Starting up database 'mssqlsystemresource'.
2009-04-17 04:40:03.12 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2009-04-17 04:40:03.21 spid5s Error: 15466, Severity: 16, State: 1.
2009-04-17 04:40:03.21 spid5s An error occurred during decryption.
2009-04-17 04:40:03.25 spid8s Starting up database 'model'.
2009-04-17 04:40:03.25 spid5s Server name is "". This is an informational message only. No user action is required.
2009-04-17 04:40:03.25 spid5s The NETBIOS name of the local node that is running the server is 'ERM-FS12'. This is an informational message only. No user action is required.
2009-04-17 04:40:03.39 spid8s CHECKDB for database 'model' finished without errors on 2009-04-17 01:00:09.603 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.40 spid8s Clearing tempdb database.
2009-04-17 04:40:03.45 Server A self-generated certificate was successfully loaded for encryption.
2009-04-17 04:40:03.48 Server Server is listening on [ (IP address) 1433].
2009-04-17 04:40:03.48 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2009-04-17 04:40:03.48 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\ERM-FS9\sql\query ].
2009-04-17 04:40:03.50 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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.
2009-04-17 04:40:03.50 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-04-17 04:40:03.51 spid13s Starting up database 'ERM'.
2009-04-17 04:40:03.51 spid12s Starting up database 'Dynamics'.
2009-04-17 04:40:03.51 spid11s Starting up database 'msdb'.
2009-04-17 04:40:03.51 spid14s Starting up database 'MBI'.
2009-04-17 04:40:03.51 spid15s Starting up database 'pcmiler'.
2009-04-17 04:40:03.51 spid16s Starting up database 'postal'.
2009-04-17 04:40:03.51 spid17s Starting up database 'powerpro'.
2009-04-17 04:40:03.51 spid18s Starting up database 'pp_training'.
2009-04-17 04:40:03.55 spid8s Starting up database 'tempdb'.
2009-04-17 04:40:03.60 spid8s CHECKDB for database 'tempdb' finished without errors on 2009-04-17 01:00:09.603 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.69 spid19s The Service Broker protocol transport is disabled or not configured.
2009-04-17 04:40:03.69 spid19s The Database Mirroring protocol transport is disabled or not configured.
2009-04-17 04:40:03.72 spid19s Service Broker manager has started.
2009-04-17 04:40:03.74 spid15s CHECKDB for database 'pcmiler' finished without errors on 2009-04-17 01:00:58.223 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.75 spid16s CHECKDB for database 'postal' finished without errors on 2009-04-17 01:00:59.743 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.78 spid16s Starting up database 'ppreports'.
2009-04-17 04:40:03.78 spid15s Starting up database 'TERM'.
2009-04-17 04:40:03.86 spid18s CHECKDB for database 'pp_training' finished without errors on 2009-04-17 01:01:26.383 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.89 spid17s CHECKDB for database 'powerpro' finished without errors on 2009-04-17 01:01:00.613 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:03.90 spid18s Starting up database 'TMBI'.
2009-04-17 04:40:03.93 spid17s Starting up database 'TFW'.
2009-04-17 04:40:04.01 spid16s CHECKDB for database 'ppreports' finished without errors on 2009-04-17 01:01:42.050 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.03 spid12s CHECKDB for database 'Dynamics' finished without errors on 2009-04-17 01:00:11.540 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.07 spid12s Starting up database 'TFWDemo'.
2009-04-17 04:40:04.11 spid16s Starting up database 'TMW_Live'.
2009-04-17 04:40:04.17 spid11s CHECKDB for database 'msdb' finished without errors on 2009-04-17 01:00:10.093 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.18 spid11s Starting up database 'TMW_Test'.
2009-04-17 04:40:04.31 spid14s Analysis of database 'MBI' (7) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2009-04-17 04:40:04.47 spid12s CHECKDB for database 'TFWDemo' finished without errors on 2009-04-17 01:02:19.057 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.59 spid12s Starting up database 'TWO'.
2009-04-17 04:40:04.69 spid11s CHECKDB for database 'TMW_Test' finished without errors on 2009-04-17 01:03:10.900 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.74 spid14s CHECKDB for database 'MBI' finished without errors on 2009-04-17 01:00:40.080 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.74 spid15s Analysis of database 'TERM' (13) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2009-04-17 04:40:04.94 spid13s CHECKDB for database 'ERM' finished without errors on 2009-04-17 01:00:14.293 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.94 spid18s CHECKDB for database 'TMBI' finished without errors on 2009-04-17 01:01:58.423 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:04.98 spid15s CHECKDB for database 'TERM' finished without errors on 2009-04-17 01:01:42.540 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:05.03 spid17s CHECKDB for database 'TFW' finished without errors on 2009-04-17 01:02:14.757 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:05.03 spid16s Recovery is writing a checkpoint in database 'TMW_Live' (17). This is an informational message only. No user action is required.
2009-04-17 04:40:05.31 spid16s CHECKDB for database 'TMW_Live' finished without errors on 2009-04-17 01:02:23.587 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:05.67 spid12s CHECKDB for database 'TWO' finished without errors on 2009-04-17 01:03:43.673 (local time). This is an informational message only; no user action is required.
2009-04-17 04:40:05.68 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.
2009-04-17 04:40:05.68 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-04-17 04:40:05.73 spid5s Launched startup procedure 'smDEX_Build_Locks'.
2009-04-17 04:44:53.25 spid54 Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2009-04-17 05:33:59.02 spid19s Service Broker manager has shut down.
2009-04-17 05:33:59.14 spid5s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2009-04-17 05:33:59.14 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
It has now done this on the hour every hour since 4:30 am. It is not failing over to the other server, just stopping the service. As soon as I click start it is back up and running.
April 17, 2009 at 1:21 pm
A couple of things you should look at and resolve, but that aren't related to your issues
2009-04-17 04:40:03.09 spid5s SQL Trace ID 1 was started by login "sa".
2009-04-17 04:40:03.11 spid5s Starting up database 'mssqlsystemresource'.
2009-04-17 04:40:03.12 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2009-04-17 04:40:03.21 spid5s Error: 15466, Severity: 16, State: 1.
2009-04-17 04:40:03.21 spid5s An error occurred during decryption.
009-04-17 04:40:00.59 Server Error: 8313, Severity: 16, State: 1.
2009-04-17 04:40:00.59 Server Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.
2009-04-17 04:40:00.59 Server Error: 3409, Severity: 16, State: 1.
2009-04-17 04:40:00.59 Server Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
I forgot to ask, do you have any scheduled jobs running anywhere that could be doing this? Is the instance failing over or just restarting? Can you look in the system event log and see who is performing the shutdown of the service (should just list as a service control but not look out of the ordinary).
April 17, 2009 at 1:35 pm
How are you starting the service? Since this is a cluster you should not be starting the service from the services applet. You need to bring the service online using cluster administrator.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply