October 29, 2010 at 12:03 pm
Hi ALL,
I am new to SQL Sever.I m facing a problem and need urgent help and suggestions.The Sql Server 2005 with SP2 breaks down for every 10mins with the below error log.
2010-10-28 11:36:58.44 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 5.2 (Build 3790: Service Pack 2)
2010-10-28 11:36:58.44 Server (c) 2005 Microsoft Corporation.
2010-10-28 11:36:58.44 Server All rights reserved.
2010-10-28 11:36:58.44 Server Server process ID is 5504.
2010-10-28 11:36:58.44 Server Authentication mode is MIXED.
2010-10-28 11:36:58.44 Server Logging SQL Server messages in file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2010-10-28 11:36:58.44 Server This instance of SQL Server last reported using a process ID of 2192 at 10/28/2010 11:36:22 AM (local) 10/28/2010 6:36:22 PM (UTC). This is an informational message only; no user action is required.
2010-10-28 11:36:58.44 Server Registry startup parameters:
2010-10-28 11:36:58.44 Server -d D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-10-28 11:36:58.44 Server -e D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-10-28 11:36:58.44 Server -l D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-10-28 11:36:58.44 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-10-28 11:36:58.44 Server Detected 16 CPUs. This is an informational message; no user action is required.
2010-10-28 11:36:58.59 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.
2010-10-28 11:36:58.59 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2010-10-28 11:36:58.62 Server Multinode configuration: node 0: CPU mask: 0x00000000000000f0 Active CPU mask: 0x00000000000000f0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-10-28 11:36:58.62 Server Multinode configuration: node 1: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-10-28 11:36:58.62 Server Multinode configuration: node 2: CPU mask: 0x0000000000000f00 Active CPU mask: 0x0000000000000f00. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-10-28 11:36:58.62 Server Multinode configuration: node 3: CPU mask: 0x000000000000f000 Active CPU mask: 0x000000000000f000. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-10-28 11:36:58.62 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-10-28 11:36:58.70 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 58228, committed (KB): 238180, memory utilization: 24%.
2010-10-28 11:36:58.86 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2010-10-28 11:36:58.86 Server Database mirroring has been enabled on this instance of SQL Server.
2010-10-28 11:36:58.88 spid11s Starting up database 'master'.
2010-10-28 11:36:59.48 spid11s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-10-28 11:36:59.54 spid11s SQL Trace ID 1 was started by login "sa".
2010-10-28 11:36:59.57 spid11s Starting up database 'mssqlsystemresource'.
2010-10-28 11:36:59.60 spid11s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2010-10-28 11:36:59.78 spid15s Starting up database 'model'.
2010-10-28 11:36:59.78 spid11s Server name is 'SQLPROD01'. This is an informational message only. No user action is required.
2010-10-28 11:36:59.78 spid11s The NETBIOS name of the local node that is running the server is 'SCTDB02'. This is an informational message only. No user action is required.
2010-10-28 11:37:00.00 spid15s Clearing tempdb database.
2010-10-28 11:37:00.04 Server A self-generated certificate was successfully loaded for encryption.
2010-10-28 11:37:00.05 Server Server is listening on [ 192.168.20.110 <ipv4> 1433].
2010-10-28 11:37:00.05 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-10-28 11:37:00.05 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\SQLPROD01\sql\query ].
2010-10-28 11:37:00.06 Server Server is listening on [ 192.168.20.110 <ipv4> 1434].
2010-10-28 11:37:00.06 Server Dedicated admin connection support was established for listening remotely on port 1434.
2010-10-28 11:37:00.10 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.10 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.75]
2010-10-28 11:37:00.10 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.10 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.75]
2010-10-28 11:37:00.11 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.11 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.75]
2010-10-28 11:37:00.11 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.11 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.75]
2010-10-28 11:37:00.11 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.11 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.55]
2010-10-28 11:37:00.11 Logon Error: 17187, Severity: 16, State: 1.
2010-10-28 11:37:00.11 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.20.55]
2010-10-28 11:37:00.12 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-10-28 11:37:00.16 spid21s Starting up database 'Report_Stage'.
2010-10-28 11:37:00.16 spid19s Starting up database 'Report'.
2010-10-28 11:37:00.16 spid22s Starting up database 'sqltrace'.
2010-10-28 11:37:00.16 spid18s Starting up database 'msdb'.
2010-10-28 11:37:00.16 spid23s Starting up database 'Archive'.
2010-10-28 11:37:00.16 spid25s Starting up database 'Weblog_Archive'.
2010-10-28 11:37:00.16 spid26s Starting up database 'CPMS'.
2010-10-28 11:37:00.16 spid27s Starting up database 'distribution'.
2010-10-28 11:37:00.16 spid24s Starting up database 'Weblog'.
2010-10-28 11:37:00.16 spid20s Starting up database 'Dashboard'.
2010-10-28 11:37:00.18 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:00.18 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.93]
2010-10-28 11:37:00.18 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:00.18 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.207]
2010-10-28 11:37:00.26 spid15s Starting up database 'tempdb'.
2010-10-28 11:37:00.29 spid15s Analysis of database 'tempdb' (2) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-10-28 11:37:00.32 spid28s The Service Broker protocol transport is disabled or not configured.
2010-10-28 11:37:00.33 spid28s The Database Mirroring protocol transport is disabled or not configured.
2010-10-28 11:37:00.34 spid28s Service Broker manager has started.
2010-10-28 11:37:00.49 spid24s Analysis of database 'Weblog' (10) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-10-28 11:37:00.73 spid24s Recovery is writing a checkpoint in database 'Weblog' (10). This is an informational message only. No user action is required.
2010-10-28 11:37:00.74 spid25s CHECKDB for database 'Weblog_Archive' finished without errors on 2009-01-27 21:50:16.100 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:00.79 spid24s CHECKDB for database 'Weblog' finished without errors on 2009-01-27 21:50:16.100 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:00.79 spid23s CHECKDB for database 'Archive' finished without errors on 2009-01-27 19:51:44.347 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:00.94 spid20s CHECKDB for database 'Dashboard' finished without errors on 2009-01-22 21:17:22.673 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:01.38 spid22s CHECKDB for database 'sqltrace' finished without errors on 2009-01-27 19:47:00.963 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:01.96 spid19s Recovery is writing a checkpoint in database 'Report' (6). This is an informational message only. No user action is required.
2010-10-28 11:37:02.08 spid19s CHECKDB for database 'Report' finished without errors on 2009-01-22 21:18:23.807 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:02.11 spid26s Analysis of database 'CPMS' (12) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-10-28 11:37:02.19 spid26s Recovery is writing a checkpoint in database 'CPMS' (12). This is an informational message only. No user action is required.
2010-10-28 11:37:02.20 spid21s Analysis of database 'Report_Stage' (7) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-10-28 11:37:02.21 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.21 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.93]
2010-10-28 11:37:02.22 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.22 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.89]
2010-10-28 11:37:02.26 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.26 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.85]
2010-10-28 11:37:02.26 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.26 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.74]
2010-10-28 11:37:02.28 spid26s CHECKDB for database 'CPMS' finished without errors on 2009-01-27 23:48:40.400 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:02.36 spid21s CHECKDB for database 'Report_Stage' finished without errors on 2009-01-27 23:48:40.400 (local time). This is an informational message only; no user action is required.
2010-10-28 11:37:02.40 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.40 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.98]
2010-10-28 11:37:02.45 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.45 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.93]
2010-10-28 11:37:02.46 Logon Error: 18456, Severity: 14, State: 16.
2010-10-28 11:37:02.46 Logon Login failed for user 'CPMS_public'. [CLIENT: 192.168.20.64]
2010-10-28 11:37:02.63 spid11s Recovery is complete. This is an informational message only. No user action is required.
2010-10-28 11:37:02.68 spid11s Launched startup procedure 'sp_MSrepl_startup'.
2010-10-28 11:37:03.04 spid78 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-10-28 11:37:03.36 spid78 Using 'xpsqlbot.dll' version '2005.90.4035' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2010-10-28 11:37:03.54 spid78 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.
2010-10-28 11:37:03.57 spid78 Using 'xplog70.dll' version '2005.90.4035' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2010-10-28 11:37:14.70 spid209 Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from c:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\.
2010-10-28 11:37:15.85 spid209 AppDomain 2 (master.dbo[runtime].1) created.
2010-10-28 11:37:15.87 spid209 Unsafe assembly 'scout.notificationssqlserverclr, version=1.0.3524.27362, culture=neutral, publickeytoken=f65af4cc077b89ea, processorarchitecture=msil' loaded into appdomain 2 (master.dbo[runtime].1).
2010-10-28 11:37:15.88 spid221 Unsafe assembly 'scout.sqldatachangenotifierclr, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 2 (master.dbo[runtime].1).
2010-10-28 11:37:16.13 spid221 Unsafe assembly 'system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil' loaded into appdomain 2 (master.dbo[runtime].1).
2010-10-28 11:37:16.58 spid221 Unsafe assembly 'smdiagnostics, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil' loaded into appdomain 2 (master.dbo[runtime].1).
2010-10-28 11:37:16.81 spid221 Unsafe assembly 'system.runtime.serialization, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil' loaded into appdomain 2 (master.dbo[runtime].1).
2010-10-28 11:49:20.06 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2010-10-28 11:49:21.58 spid28s Service Broker manager has shut down.
2010-10-28 11:49:21.58 spid11s .NET Framework runtime has been stopped.
2010-10-28 11:49:21.80 spid11s 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.
2010-10-28 11:49:21.80 spid11s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
October 29, 2010 at 12:12 pm
Unless I'm misreading the log, it looks like it was deliberately shut down.
I'd question all the unsafe assemblies being loaded right before that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 12:20 pm
EDIT: That was a dumb idea on further review.... nixing my first comment.
Check this link:
Seems to have a lot to do with the SQL Server installation, but beyond that, I don't know. Sorry.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 2:12 pm
hello GSquare this is the problem
I am having sql server 2005 cluster with 2 nodes. For every 10 minutes ( exactly10 min) the node freezes and the sql sessions drops (like from 600 to 150 sessions) i am not able to start session (its a webserver database) , so i have to stop the freezed node and start the other , and the same repeats for this node also after 10 min exactly.
Can anyone please help me to fix this.
October 29, 2010 at 2:20 pm
You are loading some CLR assembly, correct? Have you checked that the code in there might not be the problem? What changed before this started happening? A code release?
October 29, 2010 at 2:24 pm
Hi
Nothing has changed in the code.
We had a power outage at the data-center.Unfortunately the secondary power was also down the servers were down.
When we brought the cluster back online we are seeing this.
October 29, 2010 at 2:27 pm
Hub/Router fried/shorting? Heartbeat nic cards shorted? I'd look for a physical failure, not a software one, after a massive power failure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 2:30 pm
pinnamaneni.pavani (10/29/2010)
2010-10-28 11:49:21.80 spid11s 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.
It's not a crash. Someone or something is deliberately shutting SQL down from the Service manager.
Have you checked for errors in cluster admin (could cause a SQL shutdown if it thinks SQL isn't responding)? Have you done database integrity checks? Have you checked for errors in the windows event log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2010 at 2:35 pm
p.s. The last integrity check of any of these databases was almost 2 years ago. You like living dangerously?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2010 at 2:55 am
This is quite worrying too
SQL Server error log
2010-10-28 11:36:58.70 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 58228, committed (KB): 238180, memory utilization: 24%.
Check the Windows event log for more info regarding the cluster operations and post details if you can!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2010 at 4:18 am
GilaMonster (10/29/2010)
pinnamaneni.pavani (10/29/2010)
2010-10-28 11:49:21.80 spid11s 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.It's not a crash. Someone or something is deliberately shutting SQL down from the Service manager.
Have you checked for errors in cluster admin (could cause a SQL shutdown if it thinks SQL isn't responding)? Have you done database integrity checks? Have you checked for errors in the windows event log?
Looks like it, Had once the cluster service account removed in SQL while the "check if is alive" could not be done anymore while it failed over again nonstop. In this situation it looks like that something which is dependant for the cluster is failing and it iniate a failover. So you should check nics, storage connections other network related issue's. The failing is cluster related not SQL
October 30, 2010 at 8:09 am
GilaMonster (10/29/2010)
p.s. The last integrity check of any of these databases was almost 2 years ago. You like living dangerously?
That actually brings up a good subject. Do you happen to have a recommended link for what to do if an integrity check fails? I'm usually NOT the "Systems DBA" for companies I've had to work for haven't had the "pleasure" of going through such a failure but would like to "hear it" from someone that has.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2010 at 8:18 am
Jeff
There isn't really any hard fast link for a fix, it depends what the failure is. Each situation is taken on it's own merit.
For instance something I have seen a fair bit when migrating databases from SQL 2000 is a metadata failure during the DBCC check (you used to be able to edit the system tables directly). Sometimes it's as simple as finding the affected object and modifying it's definition.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2010 at 8:22 am
Jeff Moden (10/30/2010)
GilaMonster (10/29/2010)
p.s. The last integrity check of any of these databases was almost 2 years ago. You like living dangerously?That actually brings up a good subject. Do you happen to have a recommended link for what to do if an integrity check fails?
Absolutely. http://www.sqlservercentral.com/articles/65804/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2010 at 8:41 am
I certainly could be wrong but, from the following entry in the log you posted, it looks like something may be starting up on the server BEFORE SQL Server and it's not leaving enough memory for MSDTC to actually start or maybe it's set to not start. In either case, I still believe that, somewhere along the line, someone did an install of something and the server hasn't been rebooted since that install and now it's having problems.
2010-10-28 11:36:58.62 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-10-28 11:36:58.70 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 58228, committed (KB): 238180, memory utilization: 24%.
2010-10-28 11:36:58.86 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
My recommendation is to check the Windows logs and see what may be happening before the attempt to start SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply