HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation

smamm — Tue, 24 Nov 2015 20:41:54 GMT

We built a new SIM installation earlier this year (updated recently to latest version of 7.4.x), got it up and running successfully, and all was well. Then everyone involved got sidetracked on other projects while SIM ran just fine on its own. We are using SQL Express, as SIM has redundancy in our environment and we didn't feel a need to burn money on another SQL license. (The redundancy is via the HP monitoring plugin for SCOM).

Fast forward 8 months. Our SQL Express database hit the 10gb mark, which is the maximum size allowed. It cannot grow and has gotten into a problematic state.

We can logon to SIM, but there's no data in the interface. The database can be managed via SQL Management Studio. To be perfectly honest, I don't really care about historical data and wouldn't have a problem with building a new database from scratch. In the interest of going through some valuable recovery management exercises, I'd at least like to take a crack at "fixing" this.

My definition of "fix" is this: Shrink the database below 10gb and manage it better going forward to keep it clean via automated tasks. I'm aware of how to schedule tasks to delete events older than X number of days. I can't at the moment though, because the database is in a state where events cannot be managed via the application GUI (there's no data presented). I'm assuming that events also can't be managed via SIM's command line interface as well while the database is locked at its current size.

What say you? Shrink it below 10gb via SQL Management Studio, then try to access data within the interface and purge all junk older than a month to reduce its size naturally?

Re: HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation

n0kia — Wed, 09 Dec 2015 22:52:09 GMT

Backup your current SIM DB,

Stop SIM Service

Open in SQL Management Studio for the SQL server. Right click the SIM db -> Run Query

-- for information

select count (*)

from notices

where (

(noticeType != 1 ) and

(noticeSeverity = 100)

)

Also run this task to delete old task results with SQL studio:

delete from hpmxAutomationTaskResults

4) Open a command prompt on the sim server and run (from any directory) : mxconfigrepo –f

5) Restart SIM service

Run the folowing sql script to where all the used space is:

SELECT 
    s.Name AS SchemaName,    t.NAME AS TableName,    p.rows AS RowCounts,    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKBFROM 
    sys.tables tINNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_idINNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_idWHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

topic Re: HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation in Server Management - Systems Insight Manager

HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation

Re: HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation