HDS database size issues/Agent state trace?

paul jurkowski · ‎12-01-2010

ICM 7.5.9 ES4

CVP 7.x

CTIOS Server 7.5.9 Client 7.2.7 or 7.5.9

UCM 7.1.3

So this is what happens when a company finally has more than one UCCE engineer..I get to go back and find all the stuff that needed to be accomplished over the past 3 years and accomplish it.

With that said, we have the Agent State Trace option on an agent per the business request. When we run the WebView Agent State Trace report we are only getting data from about 18 hours previous. Opened a case with TAC, they said that we need to resize our DB or change our retention size. I don't buy it, at least not as simply as it was put by them.

our HDS is running at 80% capacity stating that 80% of the free space is used in the hds database. On the roggers, we are using only 10%. I'm pretty sure that our HDS has been at this capacity for some time.

What are the options? Our HDS DB size is is about 41GB in our SQL folder and ICMDBA says we are at 81.5% capacity. Do we simply increase the size in SQL, do we decrease retention in ICMDBA, etc.

The default is 1095 days, does anyone still use the default? I'm pretty sure that no one on the business ever goes back more than a year. Any suggestions would be helpful, especially about the agent state trace.

thanks

geoff · ‎12-01-2010

Obviously you have an issue with your HDS databases and need to resolve it.

1. Examine Space Used Summary from ICMDBA and run some numbers - look at the number of days each table is holding and try to figure out if going down to 365 days will get you where you want to be - between 50% and 65% full.

2. If it's true that your company is not interested in data more than 365 days old, change all the retention times in the registry to 365 and let tonight's purge dump all it needs to, and then see what the capacity is like tomorrow.

3. If you have space on the hard drives that is not being used, add another data file equal to what you have and another transaction log file. Just stop the Distributor, add the files, and restart. Trivial.

The default retention time of 3 years - nothing wrong with that default if you can provide enough space.

Regards,

Geoff

paul jurkowski · ‎12-01-2010

So don't use the ICMDBA tool just change the registry keys?

I think we can knock it down to 2 years today as I have approval from our biggest group, if they say OK they pretty much trump everyone else. They should be OK going down to 365 but I'll have to have a more serious conversation about that.

geoff · ‎12-01-2010

I just do it through the registry. It's simple.

Just go into HKEY_LOCAL_MACHINE\SOFTWARE\Cisco Systems, Inc.\ICM\\Distributor\RealTimeDistributor\CurrentVersion\Recovery\CurrentVersion\Purge\Retain\

then look at each of the main hives - Agent, HalfHour, Call Detail and go and change the number. I know the ICMDBA allows you to Estimate usage and vaguely remember something about driving the retention times for setup, but I'd rather have total control - it's a production system after all.

You could build a .reg file yourself, with 730 days as the param. If they give the ok to go to 365, search and replace and run the .reg again.

I'm a bit old school.

Regards,

Geoff

geoff · ‎12-01-2010

Do it using SQL Enterprise Manager.

What do you currently have? Say you have 2 x 32GB data file and 2 x 2GB trans logs. From SQL, add another 32GB data file and another 2GB trans log file. I like to make them all the same size - used to be required in SQL 2000, but 2005 is more flexible.

Regards,

Geoff

geoff · ‎12-01-2010

I checked again on how the Estimate part of ICMDBA allows you to press OK and write your retention times to the registry. Then I checked the registry to see which ones had not changed, and noticed that there are a few that are unaffected by the Estimate tool.

But whatever you feel comfortable with would work. I just export the whole of the Retain key and edit the file, then crank it back through the registry.

Regards,

Geoff

paul jurkowski · ‎12-01-2010

Cool thanks, haven't messed with the HDS in some time, so a little timid on the registry but I found everything. I think our best bet is to look into the DB size allocated or whatever. I'm working with our SQL admin on the windows side tomorrow, see what we can figure out. Thanks.

paul jurkowski · ‎12-01-2010

Actually, looking at it in the registry, it is all set to 395 days. We don't have dialer, basically just inbound queuing. I'm going to try the estimate tool. We have about 60 gb free on the drive, would expanding the size be the fix? Would that be through SQL or the ICMDBA?

Thanks again.

kimv · ‎12-02-2010

Guys.. I would just add...

This is all documented within Chapter 4 of the guide at the following link, specifically Page 51, "When a Database Nears Capacity"

http://www.cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_7_5/maintenance/guide/icm75ag.pdf

I will also say.. you can expand your database with either ICMDBA or directly within SQL Server Management Stuido (2005) or Enterprise Manager (2000). The difference in using one over the other is that with ICMDBA when expanding a database it will create a new data segment and spread the data over all segments. When using SQL directly you can expand the current segment. The same is accomplished but for 'neat freaks' like me... I prefer SQL Server and having my mdf in one big file. There is no need to increase the size of the log segment unless it is undersized.. and given that the recovery interval of an HDS is 1 minute, most likely it is sized fine.

Also, an important note. If you are considering removing data from your HDS, that is, changing the number of retention days configured. I STRONGLY recommend changing that number by no more than 10 or 15 days at a time. If you go, for example from 700 days to 300 days you run the risk of the purge taking too long for larger tables and affecting performance.

The way I have advised customers to be able to purge large amounts of data over the years is to have them schedule a maintenance window, say 1 hour. Then use the following procedure;

(1) Change the retention days within the registry to purge 10 - 15 days of data

HKEY_LOCAL_MACHINE\SOFTWARE\Cisco Systems, Inc.\ICM\\Distributor\RealTimeDistributor\CurrentVersion\Recovery\CurrentVersion\Purge\Retain

(2) Change the Purge Schedule from 00:30 M,T,W,Th,F,S,Su to a time a few mins from the time the registry value was changed...

HKEY_LOCAL_MACHINE\SOFTWARE\Cisco Systems, Inc.\ICM\\Distributor\RealTimeDistributor\CurrentVersion\Recovery\CurrentVersion\Purge\Schedule\Schedule

(3) Monitor the Replication log to verify that the purge completed.
http://www.cisco.com/image/gif/paws/48626/qa-daily-purge-cycle.pdf

(4) In this release the large db tables are purged via scheduled jobs within SQL Enterprise Manager via SQL Server Agent. The schedule for these jobs do not need to be changed, but the jobs need to be executed manually to run the purge at an off time. There are 7 of these jobs.

(5) Repeat this process until the desired retention days are removed

(6) When this process is complete remember to reset the schedule back to 00:30 M,T,W,Th,F,S,Su

geoff · ‎12-02-2010

Also, an important note. If you are considering removing data from your HDS, that is, changing the number of retention days configured. I STRONGLY recommend changing that number by no more than 10 or 15 days at a time. If you go, for example from 700 days to 300 days you run the risk of the purge taking too long for larger tables and affecting performance.

Thank you. That is excellent advice, and I should have considered the impact more carefully. It's just like a when doing an upgrade and you wind down the retention time gradually over a week or so in order to have a more manageable size for the EDMT.

From the 2008 Networkers Upgrade slides:

Regards,

Geoff