Need help with SQL Server 2014 Error 18456 Severity 14 State 38

Dean Egner
Dean Egner used Ask the Experts™
on
One of my clients is a single server environment, Windows server 2016 Essentials, running SQL Server (Express) 2014.  

They are running Thompson Reuters PracticeCS with the database hosted on this server.

Until about 4 weeks ago, things were running along normally.  Then, logins started failing until a variable time between 8:00 am and 8:30 am.  Once the logins start working, they work for everyone the reset of the day.  

Backups run at 12:00 am and finish within minutes.  The database is "Always on".

I have contacted the vendor and they claim that it is a SQL problem and refuse to help troubleshoot.

Any help is appreciated.  Below are the recent logs.  At 8:37, users could log in.

Date      Source      Severity      Message
9/18/2018 8:37      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:31      spid20s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:27      spid60      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:17      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:15      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:11      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:11      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.62]
9/18/2018 8:11      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 8:11      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.62]
9/18/2018 8:11      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 8:11      spid54      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:10      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:07      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:02      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 8:02      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 8:01      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 8:01      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 8:01      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:01      spid20s      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:01      spid20s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:00      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:59      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 7:59      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:59      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 7:59      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:59      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:59      spid51      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:58      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.61]
9/18/2018 7:58      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:58      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.61]
9/18/2018 7:58      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:58      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:58      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.61]
9/18/2018 7:58      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:57      spid54      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:57      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.61]
9/18/2018 7:57      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 7:57      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:57      spid51      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:47      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:37      spid52      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:30      spid28s      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:30      spid28s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:27      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:17      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:07      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 7:00      spid38s      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 7:00      spid38s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:57      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:47      spid52      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:47      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:37      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:30      spid18s      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 6:30      spid18s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:29      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 6:29      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 6:29      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.63]
9/18/2018 6:29      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: 38.
9/18/2018 6:29      spid55      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 6:28      spid54      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 6:27      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:17      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 6:07      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 5:59      spid26s      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 5:59      spid26s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 5:57      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 5:47      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 5:37      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 5:29      spid36s      Unknown      Starting up database 'CSP_203449_DBSPC'.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
Dean EgnerDirector of Professional Services

Author

Commented:
Thanks for the advice.  I have turned off the Auto-Close option and will let you know the results.

Dean
lcohanDatabase Analyst

Commented:
Starting up database....

This sounds like the SQL Server Express is restarting on that box for whatever reason and you (or someone with OS rights) need to check the Event Viewer for more details to figure out why this SQL Service restart is happening.

Also when you say that
The database is "Always on".
What are you referring to from the two definitiones below:
  • AlwaysOn Failover Clustering Instances (AlwaysOn FCI)
  • AlwaysOn Availability Groups (AlwaysOn AG)
Dean EgnerDirector of Professional Services

Author

Commented:
Thank you all for the responses!  The auto-close disable did the trick.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial