Cant start SQL Server after crash

Our SQL Server crashed this morning.  We had been getting LOW DISK SPACE messages which may be the culprit.  The Physical Server crashed, not just the SQL Server application.

Now I restarted the Server itself, and moved a few folders from C: to D: to get more space.  

However, I can not connect to SQL SERVER.  

When I try in Management Stuido, I get the message "An error has occurred while establishing a connection to the server".

If I go to a Control Panel and try to start the MSSQLSERVER service, it starts and stops.

If I go to acommand line, and type Net Start MSSQLSERVER, it says it could not be started.

HELP, HELP, HELP.  Our Systems Support guy is not in today, and users can not do their work.  
MGothelfAsked:
Who is Participating?
 
YveauCommented:
It looks like something has got an hold of the port 1433:
Server TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.

There should be a back door, this will only work local, so on the server !
    sqlcmd A
or
    sqlcmd A -E
or
    sqlcmd A -U sa -P p@ssw0rd

check out:
http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Hope this helps ...


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
check the event viewer to see the error
0
 
SQL_SERVER_DBACommented:
I think your out of storage, delete any transaction logs or backups you don't need, then restart.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
also check the ERRORLOG file on the sql server LOG folder.
0
 
MGothelfAuthor Commented:
I believe that the transaction logs and backups are on drive D:\  How can I check?
0
 
SQL_SERVER_DBACommented:
Do you have access to the D drive? If not have you network admin assign it to you.
0
 
McDizzo92Commented:
If you have established that you might be out of space and have the time you can even try to do a backup of the database to clear the transaction logs (as they commit). Just kicking this off if you have access to it and not necessarily the drives to look at might save you some time.
0
 
MGothelfAuthor Commented:
I did a backup of the databases on Friday Night.  This did not help the space problem.

Earlier, I had tried moving any non-essential file I could, and deleting unnecessary applications.  This did almost no good, as the only thing that really is on the server is SQL.

Unfortunately, when the server was set up, it had a C drive of  8GB thats almost full and a D drive of 52GB thats almost empty.
 
I looked at the drives, and temporarily moved one of the databases from C: to D:.  This freed up almost 2 GB.  I then rebooted, and it is now taking over 45 minutes.  I am assuming that this is good, that it means SQL Server has started and is running to rebuild the databases that were open when it crashed.
0
 
CragCommented:
If SQL Server doesn't start you need to look at the contents of the latest log file (ERRORLOG) which is probably on the C drive in the Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder

SQL Server doesn't rebuild databases that you move - it might start but then it'll list them as SUSPECT.
0
 
MGothelfAuthor Commented:
The server is still stalled on the Windows Startup screen, for over an hour and a half.  Is it reasonable that it would display this screen while MSSQL is rebuilding databases?  Is the time a reasonable duration?
0
 
CragCommented:
Doesn't seem like it's working, especially if it's just the Windows startup screen. You mean the splash screen with the progress bar across the bottom/middle of the graphic?
SQL Server is a service and wouldn't start until the logon box is close to being displayed.
Also failure of SQL shouldn't prevent Windows from starting.
It may be that you've moved/delete something essential for Windows.
0
 
YveauCommented:
Which databases did you move ?
You should always leave the system database where they were. That would be the files:
distmdl.ldf
distmdl.mdf
master.mdf
mastlog.ldf
model.mdf
modellog.ldf
msdbdata.mdf
msdblog.ldf
mssqlsystemresource.ldf
mssqlsystemresource.mdf
tempdb.mdf
templog.ldf

All other files are probably user databases and aren't necessary to startup SQL Server. The missing database will be marked suspect, but that's the next step. First get this thing started ...

Hope this helps ...
0
 
MGothelfAuthor Commented:
I moved a database called MG_TEST (a user database).

My thinking was that since SQL was a service, that it would be started as part of windows startup and that windows startup wouldn't complete until all services were completely started.  

Its been almost 2 hours now.  I think if SQL failed (as it did earlier), Windows would have started (as it did earlier).  I am thinking that SQL is still starting up, and that it is trying to rebuild the databases.  

0
 
CragCommented:
It might be - I would be surprised though.
You should still have got the GUI or close to the logon screen.
How much activity do your databases have?
0
 
YveauCommented:
No, when the files (both .mdf and .ldf) are gone, the database would just be marked suspect and the SQL Server would have come up with very little trouble. It doesn't look good that it takes two hours to boot a machine. And I'm pretty sure that it is not SQL Server that takes two hours to start ...

Lets say the machine will become available in a few moments and SQL is still not starting, can you for just a few moments copy the files back to the original location ? Then we can do a detach of the database, move the files to the empty drive and atach them again ... and SQL will do all the administration for you so you can keep running SQL Server with the database on the empty drive.

Hope this helps ...
0
 
CragCommented:
Can you run the eventviewer on another system and try to connect to the system that is slow in starting?
Are the disk lights working on the system you are trying to recover or has it crashed?
0
 
MGothelfAuthor Commented:
Disk lights are flashing, very regularly.  

There is not a lot of activity.  One data base is more active than the others.  It may get a couple of thousand transactions a day, three or four others would get hundreds of transactions.

It is now 423 Eastern Time.  Most users go home at 5.  I am inclined to let it run till morning, just in case, then kill the startup and do what you said.  
0
 
YveauCommented:
... keep us informed ...

0
 
MGothelfAuthor Commented:
OK - I killed Windows and started again.  This time it took just a couple of minutes.  However, when I try to connect, I still get the same error message CAN NOT CONNECT TO DATABASE.  

Here is the error log from earlier today..

2007-10-15 13:22:21.06 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
      Oct 14 2005 00:33:37
      Copyright (c) 1988-2005 Microsoft Corporation
      Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2007-10-15 13:22:21.06 Server      (c) 2005 Microsoft Corporation.
2007-10-15 13:22:21.06 Server      All rights reserved.
2007-10-15 13:22:21.06 Server      Server process ID is 1836.
2007-10-15 13:22:21.06 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-10-15 13:22:21.06 Server      This instance of SQL Server last reported using a process ID of 3204 at 10/15/2007 12:42:29 PM (local) 10/15/2007 4:42:29 PM (UTC). This is an informational message only; no user action is required.
2007-10-15 13:22:21.06 Server      Registry startup parameters:
2007-10-15 13:22:21.06 Server             -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-10-15 13:22:21.06 Server             -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-10-15 13:22:21.06 Server             -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-10-15 13:22:21.07 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-10-15 13:22:21.09 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2007-10-15 13:22:21.42 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-10-15 13:22:21.46 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-10-15 13:22:22.82 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-10-15 13:22:22.82 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2007-10-15 13:22:22.86 spid5s      Starting up database 'master'.
2007-10-15 13:22:22.95 spid5s      1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2007-10-15 13:22:22.98 spid5s      0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2007-10-15 13:22:22.98 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-10-15 13:22:23.06 spid5s      SQL Trace ID 1 was started by login "sa".
2007-10-15 13:22:23.09 spid5s      Starting up database 'mssqlsystemresource'.
2007-10-15 13:22:27.35 spid5s      Server name is 'ACMGR60SQL1'. This is an informational message only. No user action is required.
2007-10-15 13:22:27.37 spid9s      Starting up database 'model'.
2007-10-15 13:22:27.48 spid9s      Clearing tempdb database.
2007-10-15 13:22:27.68 spid9s      Starting up database 'tempdb'.
2007-10-15 13:22:27.79 spid12s     The Service Broker protocol transport is disabled or not configured.
2007-10-15 13:22:27.79 spid12s     The Database Mirroring protocol transport is disabled or not configured.
2007-10-15 13:22:27.81 Server      A self-generated certificate was successfully loaded for encryption.
2007-10-15 13:22:27.82 spid12s     Service Broker manager has started.
2007-10-15 13:22:27.82 Server      Server is listening on [ 'any' <ipv4> 1433].
2007-10-15 13:22:27.82 Server      Error: 26023, Severity: 16, State: 1.
2007-10-15 13:22:27.82 Server      Server TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.
2007-10-15 13:22:27.82 Server      Error: 17182, Severity: 16, State: 1.
2007-10-15 13:22:27.82 Server      TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-10-15 13:22:27.82 Server      Error: 17182, Severity: 16, State: 1.
2007-10-15 13:22:27.82 Server      TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-10-15 13:22:27.82 Server      Error: 17826, Severity: 18, State: 3.
2007-10-15 13:22:27.82 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-10-15 13:22:27.82 Server      Error: 17120, Severity: 16, State: 1.
2007-10-15 13:22:27.82 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the VIA protocol is enabled disable it and then try starting the SQL Server service again.
0
 
MGothelfAuthor Commented:
VIA was disabled.   As far as port 1433 is concerned, I get the same error when I change the port to 1434 or 1435.  

Also, I was not able to connect using the backdoor you specified (its DAC Dedicated Administrator Connection).

Here is error message

HResult 0x274D Level 16 State1

SQL Network Interfaces: Could not establish dedicated administrator connection (DAC) on default port.  Make suer that DAC is established [10061]

SQLCMD: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections.

SQLCMD: Error: Microsoft SQL Native Client: Login Timeout Expired.
0
 
YveauCommented:
I'm not a network expert, but there is a way to figure out what process is hijacking the 1433 port.
Try to figure that out, kill it and start SQL Server ...

Sorry I can't provide the details on this now ... I will ask my colleagues tomorrow morning for you ...
0
 
MGothelfAuthor Commented:
SOME SUCCESS.

We went to SQL Server Surface Area configuration said connect local only, and that worked.  When I try to do anything in Management Studio, I cannot. The default database (which was open when we crashed) is not available (there is no '+' next to it in the list). If I try to access anything, SQL tries to first connect to the default database.
0
 
MGothelfAuthor Commented:
I was finally able to get everything back and running.  It was a TCP issue which I won't go into here.  Once I could connect as Administrator, I was able to detach and move a few databases to D:.  That freed up enough space to allow me to work.  The only way I was able to get the default database to reappear was to restore it from backup.  
0
 
CragCommented:
Glad to hear it. sounds like a typical day - not one issue with SQL but two.
0
 
YveauCommented:
Well done ! Glad I could be of any help and thanks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.