Sql-server – Newly discovered SQL Server Express has all databases inaccessible/offline

We have several remotes sites at my company and today one of the employees came to me and said their SQL reporting tool stopped working. Up until this point I did not even know this server or database or reporting service existed!

I have RDP access to the server and can open SQL Server Management Studio 2008 R2 and the databases appear to be SQL Server Express. Currently I can login to the instance with my domain admin account but every database gives me the following message when I try to expand it:

enter image description here
The webpage they go to for the reports gives them the following error:

•   An error has occurred during report processing. (rsProcessingAborted)
    o   Cannot create a connection to data source 'DNPDataSource'. (rsErrorOpeningConnection)
        ?   For more information about this error navigate to the report server on the local server machine, or enable remote errors

When I try to view the SQL Server Error log I get this error:

enter image description here

I do not have the sa password. I'm not a DBA but need to try and figure this one out, can anyone point me in a direction to start troubleshooting this? I'm completely lost.

here is the ERROR.LOG

2013-03-27 13:14:24.34 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) 
    Jun 11 2012 16:41:53 
    Copyright (c) Microsoft Corporation
    Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2013-03-27 13:14:24.34 Server      (c) Microsoft Corporation.
2013-03-27 13:14:24.34 Server      All rights reserved.
2013-03-27 13:14:24.34 Server      Server process ID is 9040.
2013-03-27 13:14:24.34 Server      System Manufacturer: 'Intel Corporation', System Model: 'S5520UR'.
2013-03-27 13:14:24.34 Server      Authentication mode is MIXED.
2013-03-27 13:14:24.34 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2013-03-27 13:14:24.34 Server      This instance of SQL Server last reported using a process ID of 2428 at 3/27/2013 1:14:02 PM (local) 3/27/2013 7:14:02 PM (UTC). This is an informational message only; no user action is required.
2013-03-27 13:14:24.34 Server      Registry startup parameters: 
     -d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf
     -e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG
     -l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
2013-03-27 13:14:24.37 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-03-27 13:14:24.37 Server      Detected 16 CPUs. This is an informational message; no user action is required.
2013-03-27 13:14:24.51 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.
2013-03-27 13:14:24.51 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2013-03-27 13:14:24.56 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-27 13:14:24.62 spid7s      Starting up database 'master'.
2013-03-27 13:14:24.69 spid7s      2 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.69 spid7s      0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.69 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2013-03-27 13:14:24.78 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2013-03-27 13:14:24.83 spid7s      SQL Trace ID 1 was started by login "sa".
2013-03-27 13:14:24.85 spid7s      Starting up database 'mssqlsystemresource'.
2013-03-27 13:14:24.87 spid7s      The resource database build version is 10.50.2500. This is an informational message only. No user action is required.
2013-03-27 13:14:25.09 spid10s     Starting up database 'model'.
2013-03-27 13:14:25.09 spid7s      Server name is 'WCCKEMAPP\SQLEXPRESS'. This is an informational message only. No user action is required.
2013-03-27 13:14:25.21 spid10s     The tail of the log for database model is being rewritten to match the new sector size of 4096 bytes.  2560 bytes at offset 99840 in file c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\modellog.ldf will be written.
2013-03-27 13:14:25.31 spid10s     Clearing tempdb database.
2013-03-27 13:14:25.32 spid13s     A new instance of the full-text filter daemon host process has been successfully started.
2013-03-27 13:14:25.37 spid7s      Starting up database 'msdb'.
2013-03-27 13:14:25.40 Server      A self-generated certificate was successfully loaded for encryption.
2013-03-27 13:14:25.40 Server      Server is listening on [ 'any' <ipv6> 54547].
2013-03-27 13:14:25.40 Server      Server is listening on [ 'any' <ipv4> 54547].
2013-03-27 13:14:25.40 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2013-03-27 13:14:25.40 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2013-03-27 13:14:25.40 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2013-03-27 13:14:25.56 spid10s     Starting up database 'tempdb'.
2013-03-27 13:14:25.60 spid13s     The Service Broker protocol transport is disabled or not configured.
2013-03-27 13:14:25.60 spid13s     The Database Mirroring protocol transport is disabled or not configured.
2013-03-27 13:14:25.61 spid13s     Service Broker manager has started.
2013-03-27 13:14:25.77 spid7s      The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes.  2048 bytes at offset 12007424 in file c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MSDBLog.ldf will be written.
2013-03-27 13:14:25.84 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2013-03-27 13:14:25.90 Logon       Error: 17187, Severity: 16, State: 1.
2013-03-27 13:14:25.90 Logon       SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [CLIENT: ::1]
2013-03-27 13:14:25.90 Logon       Error: 17187, Severity: 16, State: 1.
2013-03-27 13:14:25.90 Logon       SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [CLIENT: 172.17.0.210]
2013-03-27 13:14:26.48 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/WCCKemAPP.WCC.LOCAL:SQLEXPRESS ] for the SQL Server service. 
2013-03-27 13:14:26.48 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/WCCKemAPP.WCC.LOCAL:54547 ] for the SQL Server service. 
2013-03-27 13:14:26.48 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-03-27 13:14:31.04 spid51      Starting up database 'ReportServer'.
2013-03-27 13:14:31.37 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:31.76 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:32.07 spid51      Starting up database 'ReportServerTempDB'.
2013-03-27 13:14:32.24 Logon       Error: 18456, Severity: 14, State: 5.
2013-03-27 13:14:32.24 Logon       Login failed for user 'drily'. Reason: Could not find a login matching the name provided. [CLIENT: 172.17.0.210]
2013-03-27 13:15:12.28 spid55      Starting up database 'DNP'.
2013-03-27 13:15:13.75 spid55      Starting up database 'DSS'.
2013-03-27 13:19:36.62 spid57      Starting up database 'ReportServerTempDB'.
2013-03-27 13:25:31.18 spid53      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:25:36.38 spid53      Starting up database 'DSSDL'.
2013-03-27 13:25:38.89 spid53      Starting up database 'DSSUSERDIR'.
2013-03-27 13:25:41.26 spid53      Starting up database 'permissionsAudit'.
2013-03-27 13:25:45.00 spid53      Starting up database 'PMKemmererProduction'.
2013-03-27 13:25:48.05 spid53      Starting up database 'PMKemmererProductionTEST'.
2013-03-27 13:26:01.57 spid54      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2013-03-27 13:26:01.58 spid54      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2013-03-27 13:26:52.10 Logon       Error: 18456, Severity: 14, State: 38.
2013-03-27 13:26:52.10 Logon       Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-03-27 13:26:53.37 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:53.60 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:53.92 spid59      Starting up database 'QuietDose'.
2013-03-27 13:26:54.16 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:54.36 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:54.66 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:54.89 spid59      Starting up database 'STX'.
2013-03-27 13:26:55.57 spid59      Starting up database 'Test'.
2013-03-27 13:26:55.76 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:55.91 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:56.08 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:56.31 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:56.52 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:56.68 spid59      Starting up database 'STX'.
2013-03-27 13:26:57.24 spid59      Starting up database 'DSSDL'.
2013-03-27 13:26:57.28 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 13:26:57.45 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 13:26:57.55 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 13:26:57.74 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 13:26:57.83 spid59      Starting up database 'STX'.
2013-03-27 13:29:36.55 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 13:39:36.57 spid56      Starting up database 'ReportServerTempDB'.
2013-03-27 13:41:59.55 Logon       Error: 18456, Severity: 14, State: 8.
2013-03-27 13:41:59.55 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2013-03-27 13:44:07.70 Logon       Error: 18456, Severity: 14, State: 8.
2013-03-27 13:44:07.70 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2013-03-27 13:49:36.57 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 13:59:36.57 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:09:36.56 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:15:50.50 spid54      Starting up database 'DSSDL'.
2013-03-27 14:15:50.75 spid54      Starting up database 'DSSUSERDIR'.
2013-03-27 14:15:51.92 spid54      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:15:52.25 spid54      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:15:52.51 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:15:52.70 spid54      Starting up database 'STX'.
2013-03-27 14:18:02.83 spid51      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:46.58 Logon       Error: 18456, Severity: 14, State: 38.
2013-03-27 14:18:46.58 Logon       Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-03-27 14:18:47.49 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:47.70 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:47.92 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:48.04 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:48.33 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:48.53 spid59      Starting up database 'STX'.
2013-03-27 14:18:49.12 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:49.33 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:49.44 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:49.60 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:49.84 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:49.98 spid59      Starting up database 'STX'.
2013-03-27 14:18:50.28 spid59      Starting up database 'DSSDL'.
2013-03-27 14:18:50.39 spid59      Starting up database 'DSSUSERDIR'.
2013-03-27 14:18:50.48 spid59      Starting up database 'ReportServer$SQLExpress'.
2013-03-27 14:18:50.53 spid59      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2013-03-27 14:18:50.66 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:18:50.73 spid59      Starting up database 'STX'.
2013-03-27 14:19:36.54 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:19:36.93 spid59      Starting up database 'ReportServerTempDB'.
2013-03-27 14:29:36.55 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:39:36.57 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 14:49:36.57 spid53      Starting up database 'ReportServerTempDB'.
2013-03-27 14:59:36.58 spid54      Starting up database 'ReportServerTempDB'.
2013-03-27 15:09:36.60 spid53      Starting up database 'ReportServerTempDB'.

I can provide older logs too if it will be beneficial. I'm reading those now from here.

Results from query select name, state_desc, is_in_standby, is_cleanly_shutdown from sys.databases;

enter image description here

Image for Kin's request

enter image description here

Best Answer

From the error log, there are failed logins, are you trying to connect using any of the below ones ?

Login failed for user 'drily'. Reason: Could not find a login matching the name provided. [CLIENT: 172.17.0.210]

Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

Also, from your screenshot, the report server databases are cleanly shutdown. Do you have auto close option turned ON ?

If you have rights on the server, then below TSQL will help you :

'RESTORE DATABASE <dbname> WITH RECOVERY' or  'ALTER DATABASE <dbname> ONLINE'

What is the output for below SQL:

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc, is_auto_close_on, is_auto_shrink_on
FROM sys.databases;

UPDATE:

I see the problem now ... that auto_close option is turned ON for report server databases. According to BOL:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

Refer to here and here for more detailed explanation.

I can provide older logs too if it will be beneficial. I'm reading those now from here.

Best Answer

Related Solutions

Sql-server – SQL Error while connecting with SQL server Authentication

Sql-server – Sql server 2008 R2 installation fails with Error: 17204

Related Question