Guest

Cisco Unified Intelligent Contact Management Enterprise

How To Clear A SQL Server Transaction Log

Cisco - How to Clear a SQL Server Transaction Log

Document ID: 20465

Updated: Mar 21, 2005

   Print

Introduction

This document describes how to clear a transaction log of the Cisco Intelligent Contact Management (ICM) database. This procedure is necessary only when you see that the transaction log is filled with SQL errors in the SQL error log. For example:

2000/11/09 03:00:25.90 spid26  Error : 1105, Severity: 17, State: 2
2000/11/09 03:00:25.90 spid26  Can't allocate space for object 'Syslogs' in 
database 'csco_awdb' because the 'logsegment' segment is full. If you ran
out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE
or sp_extendsegment to increase the size of the segment.

These messages appear in various Cisco ICM process windows.

Note: This document is valid only for Microsoft SQL Server version 6.5 with all Cisco ICM versions.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Microsoft SQL Query Utilities ( ISQL_W for Microsoft SQL Server version 6.5)

  • Microsoft Windows NT or Windows 2000

Components Used

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

  • Microsoft Windows NT or Windows 2000

  • Cisco ICM

  • Microsoft SQL Server 6.5

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.

Why Does This Happen?

SQL Server uses a transaction log to keep track of all the transactions that run at any given moment. Occasionally a transaction is complete, but SQL erroneously does not list the transaction as complete. When this situation arises, the transaction remains in the transaction log. If several such unlisted transactions accumulate, the transaction log can become full, and cause Cisco ICM services to stop.

There are three types of SQL databases in the Cisco ICM system:

  • Admin Workstation (AW) databases

  • Historical Database Server (HDS) databases

  • Logger databases

The transaction log for any of these databases can get filled to 100% capacity with transactions that are complete but unlisted.

How to Clear the Log

In order to clear a transaction log, complete these steps:

  1. Go to Start > Program Files > Microsoft SQL Server 6.5 on the system that has the problem.

  2. Launch ISQL_W.

    A connection screen opens.

  3. Type the name of the system on which you are working (for example, GEOCSCOLGRA).

  4. Click Connect.

    If the connection is successful, a query window appears.

  5. From the drop-down menu at the top of the query window, select the name of the database that has the problem (for example, csco_sideA).

  6. Type in these statements with each statement on a separate line.

    dbcc checktable (syslogs)

    dump tran <database name> with no_log

    dbcc checktable (syslogs)

    dbcc perflog

    Modify the text in angular brackets (for example, change <database name> to csco_sideA).

  7. At the top of the query window, click Execute (the green arrow).

  8. If the transaction log dump is successful, the results look like what you see here:

    Note: The percent of Log Space Used (%) in the csco_sideA database is near zero.

    Database Name       Log Size (MB)    Log Space Used (%)   Status
    -------------       -------------    ------------------   ------
    cust1_sideA         100.0            0.00195313           0     
    pubs                0.0              0.0                  1     
    msdb                2.0              0.292969             0     
    tempdb              0.0              0.0                  1     
    model               0.0              0.0                  1     
    master              0.0              0.0                  

    After you dump the transaction log, stop and restart the Cisco ICM services for that system.

Related Information

Updated: Mar 21, 2005
Document ID: 20465