[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cant start SQL Server after crash

Posted on 2007-10-15
26
Medium Priority
?
1,495 Views
Last Modified: 2008-01-09
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.  
0
Comment
Question by:MGothelf
  • 9
  • 6
  • 5
  • +4
26 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20079732
check the event viewer to see the error
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20079751
I think your out of storage, delete any transaction logs or backups you don't need, then restart.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20079753
also check the ERRORLOG file on the sql server LOG folder.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MGothelf
ID: 20079867
I believe that the transaction logs and backups are on drive D:\  How can I check?
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20079881
Do you have access to the D drive? If not have you network admin assign it to you.
0
 

Expert Comment

by:McDizzo92
ID: 20079988
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
 

Author Comment

by:MGothelf
ID: 20080379
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
 
LVL 5

Expert Comment

by:Crag
ID: 20080593
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
 

Author Comment

by:MGothelf
ID: 20080751
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
 
LVL 5

Expert Comment

by:Crag
ID: 20080838
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20080999
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
 

Author Comment

by:MGothelf
ID: 20081068
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
 
LVL 5

Expert Comment

by:Crag
ID: 20081118
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20081136
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
 
LVL 5

Expert Comment

by:Crag
ID: 20081157
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
 

Author Comment

by:MGothelf
ID: 20081255
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20081273
... keep us informed ...

0
 

Author Comment

by:MGothelf
ID: 20081395
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20081431
If the VIA protocol is enabled disable it and then try starting the SQL Server service again.
0
 
LVL 18

Accepted Solution

by:
Yveau earned 2000 total points
ID: 20081500
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
 

Author Comment

by:MGothelf
ID: 20081757
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20081818
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
 

Author Comment

by:MGothelf
ID: 20084720
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
 

Author Comment

by:MGothelf
ID: 20085358
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
 
LVL 5

Expert Comment

by:Crag
ID: 20085681
Glad to hear it. sounds like a typical day - not one issue with SQL but two.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20087460
Well done ! Glad I could be of any help and thanks for the grade !
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Integration Management Part 2
Screencast - Getting to Know the Pipeline

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question