SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server crashing, cannot connect w/ SSMS, agent not starting


SQL Server crashing, cannot connect w/ SSMS, agent not starting

Author
Message
e_chow
e_chow
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 174
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:BigGrinumpInternal
sqlservr!CDmpDump:BigGrinump
sqlservr!CImageHelper:BigGrinoMiniDump
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:Tonguearam::Execute
sqlservr!SOS_Scheduler::RunTask
sqlservr!SOS_Scheduler:TonguerocessTasks
sqlservr!SchedulerManager::WorkerEntryPoint
sqlservr!SystemThread::RunWorker
sqlservr!SystemThreadDispatcher:TonguerocessWorker
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.
Matt Crowley
Matt Crowley
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 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)?
e_chow
e_chow
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 174
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search