Link to home
Create AccountLog in
Avatar of Dean Egner
Dean EgnerFlag for United States of America

asked on

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

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'.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Dean Egner

ASKER

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

Dean
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)
Thank you all for the responses!  The auto-close disable did the trick.