Setup Wizard does not accept new license, Add new user fails on “Schema modification”, database marked as "SUSPECT"

Issue

The database on this machine was marked as “SUSPECT” which most likely means it was corrupt. The most common cause of this issue is a power outage or Windows suddenly crashing/rebooting.

Scenario

Setup Wizards shows:

• Database section as good (green check-marks)
• Licensing section as failed (red x)

Se Logs show:

14:01:48.408  4236 <BasicInf> Running database worker

14:01:48.408  4236 <BasicInf> ===== BEGIN DatabaseWorker.OnPerformWork() =====

14:01:48.408  4236 <BasicInf> Warning: ResetFarmID() was called and will set iFarmID to 0. Previous iFarmID was 0

14:01:48.408   312 <BasicInf> WorkerStatus: Database: Processing: Not changing database connection string.

14:01:48.408   312 <BasicInf> WorkerStatus: Database: Processing:

14:01:52.433  4236 <Error   > DBConnection: Could not open SQL connection from pool after 3 attempts: System.Data.SqlClient.SqlException: Cannot open database "aira" requested by the login. The login failed.

Login failed for user 'HUMBLECIVIC\Admin'.

   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at Aimetis.Symphony.Setup.Database.DBConnection.OpenSqlConnectionWithRetries(String connectionString, Int32 retries, Boolean clearConnectionPool)

14:01:52.433  4236 <Error   > IsUpgradeRequired failed System.Data.SqlClient.SqlException: Cannot open database "aira" requested by the login. The login failed.

Login failed for user 'HUMBLECIVIC\Admin'.

   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at Aimetis.Symphony.Setup.Database.DBConnection.OpenSqlConnectionWithRetries(String connectionString, Int32 retries, Boolean clearConnectionPool)

   at Aimetis.Symphony.Setup.Database.DBVerifier.IsUpgradeRequired(String databaseName, Boolean& checksumSettingExists, String& currentChecksum)

14:01:52.433   312 <BasicInf> WorkerStatus: Database: Processing: Creating or modifying the database schema.

14:01:52.433  4236 <BasicInf> DBConnection: Sql connection opened successfully.

14:01:52.869   312 <BasicInf> WorkerStatus: Database: CompletedWithError: Failed to complete necessary schema modifications.  This should be addressed before continuing.

14:01:52.869  4236 <Error   > System.Data.SqlClient.SqlException: Database 'aira' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Aimetis.Symphony.Setup.Database.DBCreator.CreateOrAlterDatabase(String databaseName, Boolean checksumSettingExists, String currentChecksum)

   at SetupWizard.Workers.DatabaseWorker.DatabaseConfigurationWorker(String database)

14:01:52.869  4236 <BasicInf> DatabaseWorker.OnPerformWork() completed with status: False

14:01:52.869  4236 <BasicInf> ===== END DatabaseWorker.OnPerformWork() =====

Solution

Following the (TSQL) recovery instructions here:

http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html

http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P2.html

Instructions

Task 1: Launch OSQL

1. From the Windows Start menu.
2. Enter cmd in the search programs and files field.
3. Right-click on cmd.exe and select Run as administrator.

Task 2: Enter the following command

OSQL -S localhost\aimetis –E

OR

OSQL -S localhost\aimetis -U username –P password

E is for trusted meaning Integrated authentication (current user)

Task 3: Verify the database is suspect

USE master
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES WHERE STATE_DESC='SUSPECT'
GO

Task 4: Bring Database into emergency mode to repair it

USE master
GO
ALTER DATABASE AIRA SET EMERGENCY
GO

Task 5: Check the Databases integrity

DBCC CHECKDB (AIRA)
GO

Task 6: Set the Database to single user mode

ALTER DATABASE AIRA SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Task 7: Repair the Database

DBCC CHECKDB (AIRA, REPAIR_ALLOW_DATA_LOSS)
GO

Task 8: Return Database to multi-user mode

ALTER DATABASE AIRA SET MULTI_USER
GO