How to repair a SQL Server 2005 Suspect database

Posted by Manoj Garg on July 17, 2008

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Solution for MSDTC Error While Submit Account of Point of Sale in SQL 2008 R2

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click SQL Server Services.
3. In the details pane, right-click the name of the SQL Server instance for which you want to change the service startup account, and then click Properties.
4. In the SQL Server <instancename> Properties dialog box, click the Log On tab, chose Built in Account and select Local System as account type.
5. After selecting the new service startup account, click OK.
   A message box asks whether you want to restart the SQL Server service.
6. Click Yes, and then close SQL Server Configuration Manager.

ActiveX component can't create object - Error

Cause 1: DAO is not properly registered

Resolution

1. On the computer that hosts Microsoft Windows Terminal Server or the computer on which Microsoft Access is installed, click Start, and then click Run.
2. Type regsvr32 followed by the path to your DAO file. Enclose this path in quotation marks. For example, to register the DAO 3.6 library, use the following command:
   regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL"

Cause 2: One or more references are missing

Resolution

1. Open the database for which you receive the error message.
2. Press ALT+F11 to open the Microsoft Visual Basic Editor.
3. In the Visual Basic Editor, click References on the Tools menu.
4. Examine the list of libraries. If any items are marked as "missing," locate the library file and re-associate the reference. Or, click to clear the check box next to the library name.

Cause 3: There is a utility database reference that is not valid

Resolution

1. Open the database for which you receive the error message.
2. Press ALT+F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click References on the Tools menu.
4. Click to clear the check box next to any reference to Utility Database or Utility.mda