Microsoft Dynamics AX: Repair a Suspect Database in SQL Server 2012

Problem

The Microsoft Dynamics AX Object Server 6.2$01-MicrosoftDynamicsAX service terminated with service-specific error Code 90.
Suspect state of SQL Server database is a state when you are unable to connect to the database.
In this state you cannot do anything with your database: no opening, no backup and no restore.

Root Cause

Possible cause for this problem can be one of the following:

  • database is corrupted
  • database files are being “opened” or held by some process (operating system, other program(s)…)
  • not enough disk space for SQL Server
  • insufficient memory (RAM) for SQL Server
  • unexpected SQL Server shutdown caused by power failure

Solution

Solution for this problem is quick and  following is the detail (Execute SQL in Given Order):

  1. Connect to your database server using Microsoft SQL Server Management Studio
  2. Execute the following SQL script in given order:

NOTE: replace [DatabaseName] with your database name

-- This query will rollback any transaction which is running on that database
-- and bring SQL Server database in a "single user" mode
ALTER DATABASE MicrosoftDynamicsAX SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Marking database READ_ONLY, disable logging,
-- and limiting access only to members of the sysadmin fixed server role
ALTER DATABASE MicrosoftDynamicsAX SET EMERGENCY
-- Checks the logical and physical integrity of all the objects in the specified database
DBCC checkdb(MicrosoftDynamicsAX)
-- In case of simple failure then we can use allow data loss option to recover Database
--  DBCC CheckDB (MicrosoftDynamicsAX, REPAIR_ALLOW_DATA_LOSS)
 -- Set database accessibility to it's original state, allowing all logins
ALTER DATABASE MicrosoftDynamicsAX SET MULTI_USER
EXEC sp_resetstatus MicrosoftDynamicsAX
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s