N-able Report Manager's ETL logs show "Cannot open database requested by the login. The login failed", database is in suspect mode

Last Modified

Wed Sep 18 13:49 GMT 2019

Description

  • N-able Report Manager's Etl.DS001.log shows in this example an issue with the staging database:
    • Cannot open database "staging" requested by the login. The login failed
  • Within SQL Server Management Studio, it shows the staging database is in suspect mode

Environment

  • N-able N-central
  • N-able Report Manager

Solution

  • Suspect Mode for a database can occur for a variety of different reasons, for example:
    • Deleted/misplaced a data/log file
    • SQL Server could not access or place an exclusive lock on the data or log file while coming online
    • Database is in suspect because of a corrupted transaction
  • This may be recoverable by resetting the status for the staging database (if this is affecting another database then replace the database name accordingly):
    • EXEC sp_resetstatus 'staging'
    • ALTER DATABASE staging SET EMERGENCY
    • DBCC CheckDB ('staging')
    • ALTER DATABASE staging SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    • DBCC CheckDB ('staging', REPAIR_ALLOW_DATA_LOSS)
    • ALTER DATABASE staging SET MULTI_USER
  • If that does not work, then it is advised to perform a reinstall of N-able Report Manager, where by also removing the ODS, config, staging and warehouse databases:
    1. Uninstall N-able Report Manager software from the server.
    2. Delete the ODS, config, staging and warehouse databases from SQL server using SQL studio.
    3. Install the latest version of N-able Report Manager software that is supported by your system software.
      • Review release notes for windows server/SQL server version compatibility.
    4. After installation completes, add your N-able N-central server as a datasource
  • More information on suspect mode, and how to resolve, can be found in the articles below: