There was a brief power outage a few days ago where i live, and when i rebooted my Windows 7 computer with MS SQL 2008 R2 on it, everything seems to be working fine, except the MS SQL service wont start. I'm pretty sure no one was even using the database when the power went out.
When using SQL Server Configuration Manager to try and start the service again it says:
The request failed, or the service did not respond in a timely fashion.
Consult the log or other applicable error logs for details.
When running sqlsrvr.exe, it says that it is resorting my database, and then it says it is done restoring it, but it just sits there and does nothing for hours, and the service never gets started.
Please Help!
Update:
So when starting it through the configuration monitor, nothing gets put in the error log, but when running the sqlservr.exe, this is what it outputs, then just sits there doing nothing:
2010-11-07 16:06:32.83 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
2010-11-07 16:06:32.83 Server (c) Microsoft Corporation.
2010-11-07 16:06:32.83 Server All rights reserved.
2010-11-07 16:06:32.83 Server Server process ID is 2468.
2010-11-07 16:06:32.83 Server System Manufacturer: 'Gigabyte Technology Co., Ltd.', System Model: 'EP43-UD3L'.
2010-11-07 16:06:32.83 Server Authentication mode is MIXED.
2010-11-07 16:06:32.83 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-11-07 16:06:32.83 Server This instance of SQL Server last reported using a process ID of 3716 at 11/6/2010 4:19:02 PM (local) 11/6/2010 11:19:02 PM (UTC). This is an informational message only; no user action is required.
2010-11-07 16:06:32.83 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2010-11-07 16:06:32.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-11-07 16:06:32.84 Server Detected 4 CPUs. This is an informational message; no user action is required.
2010-11-07 16:06:32.94 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-11-07 16:06:32.95 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-11-07 16:06:32.98 spid7s Starting up database 'master'.
2010-11-07 16:06:33.07 spid7s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2010-11-07 16:06:33.08 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2010-11-07 16:06:33.08 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-11-07 16:06:33.17 spid7s Resource governor reconfiguration succeeded.
2010-11-07 16:06:33.17 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2010-11-07 16:06:33.17 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2010-11-07 16:06:33.18 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-11-07 16:06:33.21 spid7s SQL Trace ID 1 was started by login "sa".
2010-11-07 16:06:33.21 spid7s Starting up database 'mssqlsystemresource'.
2010-11-07 16:06:33.22 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2010-11-07 16:06:33.35 spid11s Starting up database 'model'.
2010-11-07 16:06:33.35 spid7s Server name is 'XXXXXXX'. This is an informational message only. No user action is required.
2010-11-07 16:06:33.42 spid11s Clearing tempdb database.
2010-11-07 16:06:33.48 spid13s A new instance of the full-text filter daemon host process has been successfully started.
2010-11-07 16:06:33.49 spid14s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.49 spid15s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.49 spid13s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.49 spid16s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.49 spid17s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.49 spid19s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.50 spid18s Starting up database 'XXXXXXX'.
2010-11-07 16:06:33.51 Server A self-generated certificate was successfully loaded for encryption.
2010-11-07 16:06:33.51 Server Server is listening on [ 'any' <ipv6> 1433].
2010-11-07 16:06:33.51 Server Server is listening on [ 'any' <ipv4> 1433].
2010-11-07 16:06:33.52 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-11-07 16:06:33.52 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2010-11-07 16:06:33.52 Server Server is listening on [ ::1 <ipv6> 1434].
2010-11-07 16:06:33.52 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2010-11-07 16:06:33.52 Server Dedicated admin connection support was established for listening locally on port 1434.
2010-11-07 16:06:33.52 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-11-07 16:06:33.58 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-11-07 16:06:34.28 spid15s 1 transactions rolled forward in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
2010-11-07 16:06:34.56 spid7s 0 transactions rolled back in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
2010-11-07 16:06:34.56 spid7s Recovery is writing a checkpoint in database 'ReportServerTempDB' (6). This is an informational message only. No user action is required.
2010-11-07 16:06:35.26 spid11s Starting up database 'tempdb'.
2010-11-07 16:06:35.59 spid13s The Service Broker protocol transport is disabled or not configured.
2010-11-07 16:06:35.59 spid13s The Database Mirroring protocol transport is disabled or not configured.
2010-11-07 16:06:35.64 spid13s Service Broker manager has started.
2010-11-07 16:06:38.52 spid7s Recovery is complete. This is an informational message only. No user action is required.
Here is the only warning that i recieve when the computer boots up. The source of it is User Profile Services (as per the Event Viewer):
Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards.
DETAIL -
6 user registry handles leaked from \Registry\User\S-1-5-21-1327984882-2424197892-1977549990-1000:
Process 616 (\Device\HarddiskVolume2\Windows\System32\lsass.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000
Process 616 (\Device\HarddiskVolume2\Windows\System32\lsass.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000
Process 980 (\Device\HarddiskVolume2\Windows\System32\svchost.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000\Printers\DevModePerUser
Process 616 (\Device\HarddiskVolume2\Windows\System32\lsass.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000\Software\Microsoft\SystemCertificates\Disallowed
Process 616 (\Device\HarddiskVolume2\Windows\System32\lsass.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000\Software\Microsoft\SystemCertificates\My
Process 616 (\Device\HarddiskVolume2\Windows\System32\lsass.exe) has opened key \REGISTRY\USER\S-1-5-21-1327984882-2424197892-1977549990-1000\Software\Microsoft\SystemCertificates\CA
After this, the next event log says that the User Profile Service has stopped (Information log).
Update 2:
I tried restoring to a restore point from before the power outage, but that didn't help anything. Also, when running sqlservr.exe it turns out that the sql server actually is working when i run that, i just never bothered to check. So as long as that command window stays open, the sql server is accessible, but i would like to get the service working again. Do you guys suggest backing up my database and reinstalling SQL server?
Update 3:
Here are the system event errors when trying to start the service:
Error1:
The MSSQLSERVER service was unable to log on as .\Username with the currently configured password due to the following error:
Logon failure: unknown user name or bad password.
To ensure that the service is configured properly, use the Services snap-in in Microsoft Management Console (MMC).
Error 2:
The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The service did not start due to a logon failure.
Best Answer
Could you be having a disk problem? Are you writing your logs or indexes to another volume that may be full? Or maybe there's not enough free space on the db volume to do the recovery?