Guest

Cisco Unified Intelligent Contact Management Enterprise

Database has been Marked as Suspect

Document ID: 26780

Updated: Apr 13, 2005

   Print

Introduction

This document describes why Microsoft SQL Server marks a database as suspect when the server is unable to access the database, and provides solutions to this problem.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Microsoft SQL Server versions 6.5 and 7.0

  • Microsoft SQL Server query utilities (ISQL_w for version 6.5, or Query Analyzer for version 7.0)

Components Used

The information in this document is based on these software and hardware versions:

  • Cisco Intelligent Contact Management (ICM) running Microsoft SQL Server version 6.5 or 7.0

  • All hardware platforms that run the Cisco ICM product with Microsoft SQL Server installed

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Problem

Microsoft SQL Server marks a database as suspect if it is unable to access that database. This means Microsoft SQL Server has set one of the bits in the Status field on the system databases table. When the database has been marked suspect, you must reset the status.

Solution 1

Refer to the Microsoft Knowledge Base for information on how to reset the suspect status. Try the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, execute the instsupl.sql script in order to create this procedure. This script resides in the Mssql\Install directory.

Note: For more information on sp_resetstatus, refer to the "Resetting the Suspect Status" topic in the Microsoft SQL Server Books Online.

First Option

One way to solve this problem is to execute sp_resetstatus in the master database for the suspect database. Complete these steps:

  1. Click Start > SQL Server Program Group.

  2. Select ISQL_w, if you use SQL Server version 6.5. Alternatively, select Query Analyzer, if you use SQL Server version 7.0.

  3. Connect to the Logger.

  4. In the query window, write and execute:

    • Use master

    • GO

    • sp_resetstatus <db_name>

  5. Exit ISQL_w or the Query Analyzer.

  6. Click Start > SQL Server Program Group.

  7. Stop and restart Microsoft SQL Server related services.

  8. Verify whether the database is available.

Second Option

If the first solution does not resolve your problem, you must restore the bit manually in the Status field. Complete these steps:

  1. Click Start > SQL Server Program Group.

  2. Select ISQL_w, for Microsoft SQL Server version 6.5 or Query Analyzer for SQL Server version 7.0.

  3. Connect to the Logger.

  4. In the Query window, write and execute:

    • sp_configure "allow updates", 1

    • reconfigure with override

    • Go

    • update sysdatabases set status = status ^ 256 where name = ""

    • sp_configure "allow updates", 0

    • reconfigure with override

    • Go

  5. Exit ISQL_w or Query Analyzer.

The database must now be in recovery mode through Microsoft SQL Server. If you interrupt this process, the database becomes marked as suspect again. You must wait until the process is complete before you synchronize loggers through ICMDBA (the old ICRDBA). If you continue to experience the problem, drop and create the database again.

Note: This solution works well with Microsoft SQL Server version 7.0. However, this solution does not always work with SQL Server version 6.5.

Solution 2

Complete these steps in order to resolve the issue:

  1. Manually delete the CDRs using the procedure in Cisco CallManager: Manually Deleting Call Detail Records (CDRs) Without the Administrative Reporting Tool (ART).

  2. Go to SQL Enterprise Manager, select Tools > SQL Server Query Analyzer.

    Note: Make sure you are running Query Analyzer from the right database server.

  3. From the Query Analyzer window, go to the main SQL Query Analyzer window and select File >Open.

  4. Open C:\Program Files\Cisco\Bin\CDR.sql and select Query > Execute to run the query. You can also click the green arrow on the toolbar or press F5 to run the query.

    This creates the CDR database.

  5. Go to the SQL Enterprise Manager and select Microsoft SQL Servers > SQL Server Group > local > Databases > CDR > Users. Then right-click and select New Database User.

  6. From the Login name pull-down menu, select CiscoCCMCDR (only if CiscoCCMCDR not already there) and make sure that public and db_owner are checked.

  7. Go to Start > Programs > Microsoft SQL Server > Enterprise Manager > PUBLISHER > Databases. Right-click on CDR > All Tasks > Detach Database and click OK.

  8. Go to Start > Programs > Microsoft SQL Server > Enterprise Manager > PUBLISHER. Right-click on Databases > All Tasks > Attach Database > C:\Program Files\Microsoft SQL Server\MSSQL\data\CDR.mdf <>Transaction Log == C:\Program Files\Microsoft SQL Server\MSSQL\data\CDR_log.mdf <> Database Owner == PUBLISHER\Administrator.

  9. Restart the server.

Related Information

Updated: Apr 13, 2005
Document ID: 26780