- Integrated Systems
- About Us
- Integrated Systems
- About Us
11-24-2015 12:41 PM
HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation
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?
12-09-2015 02:52 PM
Re: HP SIM 7.4 shrinking tips - related to database size & SQL Express limitation
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 (*)
(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