Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server crashing, cannot connect w/ SSMS, agent not starting Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2014 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 37, Visits: 118
Problems:

1) Cannot connect via SSMS
- Tried both sa account and domain account (that is in sysadmin group)
- If I supplied the wrong password, then SQL Server respond I have invalid credential.
- If I supplied the correct password/use integrated security, I get the following error message: "A severe error occurred on the current command. The results, if any, should be discarded. (.Net SqlClient Data Provider)

2) SQL Server Agent not starting
- Tried to start service with different accounts (with sysadmin privilege)
- No error from the UI
- SQLAGENT.OUT

2014-03-06 15:48:26 - ? [393] Waiting for SQL Server to recover databases...
2014-03-06 15:48:36 - ! [165] ODBC Error: 0, Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]
2014-03-06 15:48:36 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2014-03-06 15:48:36 - ! [165] ODBC Error: 0, Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]
2014-03-06 15:48:36 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
2014-03-06 15:48:36 - ? [098] SQLServerAgent terminated (normally)

3) SQL Server (seems to be) crashing all the time.
- Service started without any error from the UI
- New error log / mini dump created every minute
- Log file shows:

2014-03-02 20:11:15.92 Server Error: 17310, Severity: 20, State: 1.
2014-03-02 20:11:15.92 Server A user request from the session with SPID 51 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2014-03-02 20:11:29.90 spid51 Using 'dbghelp.dll' version '4.0.5'
2014-03-02 20:11:29.90 spid51 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0052.txt
2014-03-02 20:11:29.90 spid51 SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2014-03-02 20:11:29.90 spid51 * *******************************************************************************
2014-03-02 20:11:29.90 spid51 *
2014-03-02 20:11:29.90 spid51 * BEGIN STACK DUMP:
2014-03-02 20:11:29.90 spid51 * 03/02/14 20:11:29 spid 51
2014-03-02 20:11:29.90 spid51 *
2014-03-02 20:11:29.90 spid51 *
2014-03-02 20:11:29.90 spid51 * Exception Address = 000000007814E250 Module(MSVCR80+000000000001E250)
2014-03-02 20:11:29.90 spid51 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2014-03-02 20:11:29.90 spid51 * Access Violation occurred reading address 000000180BAE6B40
2014-03-02 20:11:29.90 spid51 * Input Buffer 48 bytes -
2014-03-02 20:11:29.90 spid51 * exec sp_oledb_ro_usrname

I opened the crash dump and found the following
0:000> kc 51
Call Site
ntdll!ZwWaitForSingleObject
kernel32!WaitForSingleObjectEx
sqlservr!CDmpDump::DumpInternal
sqlservr!CDmpDump::Dump
sqlservr!CImageHelper::DoMiniDump
sqlservr!ContextDumpNoStackOverflow
sqlservr!ContextDump
sqlservr!stackTraceExceptionFilter
sqlservr!ex_trans_cexcept
sqlservr!SOS_SEHTranslator
msvcr80!CallSETranslator
msvcr80!_CxxExceptionFilter
msvcr80!_CxxExceptionFilter
msvcr80!_InternalCxxFrameHandler
msvcr80!_CxxFrameHandler
ntdll!RtlpExecuteHandlerForException
ntdll!RtlDispatchException
ntdll!KiUserExceptionDispatcher
msvcr80!memcpy
sqlservr!yyparse
sqlservr!sqlpars
sqlservr!CSQLSource::FParse
sqlservr!CSQLSource::FCompWrapper
sqlservr!CSQLSource::Transform
sqlservr!CSQLSource::Execute
sqlservr!process_request
sqlservr!process_commands
sqlservr!SOS_Task::Param::Execute
sqlservr!SOS_Scheduler::RunTask
sqlservr!SOS_Scheduler::ProcessTasks
sqlservr!SchedulerManager::WorkerEntryPoint
sqlservr!SystemThread::RunWorker
sqlservr!SystemThreadDispatcher::ProcessWorker
sqlservr!SchedulerManager::ThreadEntryPoint
msvcr80!endthreadex
msvcr80!endthreadex
kernel32!BaseThreadStart

I think problem #1 and #2 is caused by #3. I have no idea what SPID51 is as I cannot even login with SSMS.

Fortunately we have a backup of the server and that was running fine.
Post #1549805
Posted Tuesday, March 11, 2014 2:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
SPID 51 is usually a user process, but in this case, it is likely SQLAgent. Try just starting SQL Server alone without SQLAgent. If that does not work, try starting SQL Server with a minimal configuration (-f startup parameter). This should get the server up to a point where you can log in to look for problems (run DBCC commands, query DMVs, etc.), but it will not be good to run your applications on this. Are you on the latest service pack (SP4)?
Post #1549929
Posted Wednesday, March 12, 2014 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 37, Visits: 118
Thanks. I have tried the following but it still won't login with sa in SSMS

- disabled all SQL services, except the main one
- -f -T3608
- start the SQL service with domain admin and local system
- disabled the network adapter just in case there is some unknown thing connecting to it
- disabled all other services on the server

Nothing fixed issue #3. From the log it looks like all the databases started up with CHECKDB returns no error.
Post #1550343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse