This document explains why the "SQL 1105" error message occurs, identifies whether the error relates to the database or the transaction log, and provides a possible solution.
Cisco recommends that you have knowledge of these topics:
Cisco Intelligent Contact Management (ICM)
Microsoft SQL Server
The information in this document is based on these software and hardware versions:
Cisco ICM version 4.6.2 and later
Microsoft SQL version 6.5 and 7.0
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.
Refer to Cisco Technical Tips Conventions for more information on document conventions.
When you encounter the "SQL 1105" error message, you can sometimes not identify whether the database is full or the transaction log is full.
When the Cisco ICM database runs out of space for data, the "Msg 1105, Level 17, State 1" error message appears. This message indicates that SQL Server cannot allocate space because the default segment is full. This can result in a corrupt database. You can replace a corrupt database with a previously backed-up copy. If the database is full, you can purge the older data or increase the size of the database. Cisco ICM protects against this situation with the Purge Adjustment and Auto Purge functions, and with Alarm Trackers similar to this:
HDS1: Begin Automatic Purge: 95% of the available data space is used in the xxx_hds database.
When the transaction log runs out of space, the "Msg 1105, Level 17, State 2" error message appears. This message indicates that SQL Server cannot allocate space because the log segment is full. In order to solve this problem, you can either increase the size of the transaction log or clear the transaction log. Refer to How To Clear A SQL Server Transaction Log for more information on how to clear the transaction log.
When either the State 1 or State 2 error occurs, SQL Server does not process against the database anymore, and an Alarm Tracker event appears. You can see the errors in the SQL error log file, which usually resides in the \mssql\log\errorlog directory. At a DOS command prompt, issue the cd command to change to this directory, and issue the type errorlog command to list the error log.
Therefore, State 1 in the 1105 error message means the database is full, while State 2 indicates the transaction log is full. Here are examples of each type of error message:
Error : 1105, Severity: 17, State: 1 Can't allocate space for object '6' in database 'xxx_sideA' because the 'system' 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.
SQL Server System Error: 1105, State 2, Severity: 17, Message: Can't allocate space for object 'Syslogs' in database 'xxx_sideA' 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. SQL SERVER DATABASE xxx_sideA IS OUT OF SPACE.
Note: xxx represents the instance name.
Every SQL Server database has a transaction log that records database changes. The Cisco ICM databases on the Database Servers, Historical Data Servers (HDS), and Distributor Administrative Workstations (AW) have a transaction log that is allocated on disk and is separate from the Cisco ICM data.
Every time you insert or delete a database row, SQL Server writes that row to the transaction log. When you update a row, SQL Server logs both the old and new data to the log. When you create new objects, modify permissions, add users, and allocate space for tables, SQL Server records the relevant transaction in the log.
When the transaction log fills up completely, changes to the database are disabled. Therefore, the amount of space available in the transaction log is a critical resource to the Cisco ICM database, which you must manage closely.
You set the size of the transaction log when you create a database. The size of the transaction log depends on your database size. In general, the size of the transaction log for AW DB must be from 100 MB to 200 MB and the transaction log size for the Logger and HDS must be from 200 MB to 500 MB.
Cisco recommends that you create Cisco ICM databases so that the data and the log reside on separate SQL devices. If you place the data and the log on the same device, they compete for space. When you separate the data and the log, performance improves. This allows you to manage space more efficiently. If the database runs out of space, the generated error message tells you whether the data storage area is full, or the transaction log is full. If you combine the data and log, you are not notified when the data storage area or the transaction log is full.
The Cisco Support Community is a forum for you to ask and answer questions, share suggestions, and collaborate with your peers.
Refer to Cisco Technical Tips Conventions for information on conventions used in this document.