database size of UIM keeps increasing !!!

[PhN]

Hello,
I need some urgent advices related to the DB of UIM because one of my client claims that the size of the DB keeps increasing even after we've lowered the retention days within the data_engine.

They also said that a shrink did not free up disk space !!

Are there other actions we can take from UIM perspective apart from running daily a maintenance + adjust retention days within the data_engine ?

Regards,
PhN

[Gene Howard]

Those are the action you need to take.
Most likely the data_engine deletes are not finishing.
This could be due to bad index, slow performance on the hardware.
Make sure in the data_engine the maintenance time out is set to the following using raw config.
data_management_timeout = 65534

below are some KB articles that might help
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=34940
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=102927
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=124018
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=33592
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=136679

You also may want to have your DBA check the index health on the tables in the database and run the standard report for larges tables to see where your problem is.

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[David Michel]

What is the revision of UIM?
is the audit probe used? 
Which tables are the largest?

------------------------------
Support Engineer
Broadcom
------------------------------

[Yu Ishitani]

You may want to double check "what" is causing unexpected growth.
Data file or log file ?

[Daniel Blanco]

Hi PhN do you have a DB Backup in place to shrink the transaction logs on a daily basis? 

Also run this SQL Query to find the top tables by size then figure out what probes are populating those tables. Look how those probes are configured and adjust the polling, metrics QoS your collecting, etc... 
------
--====== Query to get DB Table Sizes ================
--DROP TABLE #temp
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
go
------

------------------------------
Daniel Blanco
Enterprise Tools Team Architect
[e-mail]
------------------------------

[James Christensen]

Are there an abnormally high number of alarms or alarm suppression counts? If there are high suppression counts on a large number of alarms, then that indicates a need for a review of the alarming and thresholding scheme (Monitoring governance) as the ones with high suppression counts are situations that are not being resolved, most likely because they are not urgent enough to be considered actionable. For every count, there is a record in the nas transaction log table. Even if you have the retention setting set low (example: 7 days) in the NiS bridge configuration, these records will not be deleted until the alarm is cleared (the timer doesn't start until the alarm is no longer an active alarm, i.e. acknowledged/closed). This is usually the first thing I check when performing an evaluation of the installation and the health of the implementation and the processes surrounding event management.

------------------------------
[Designation]
[City]
------------------------------