Question
How do I determine what the database table sizes are in the Altiris database per solution?
Answer
You can run the following SQL script in SQL Query Analyzer using the Altiris database to determine which tables are the largest and their respective Altiris solution:
/*
/ Database Table Size SORTING USAGE int VALUES
/ 1 = Alphabetically by table name [Table_Name]
/ 2 = Sorted by Row Count
/ 3 = Sorted by Reserved Space in KB's
/ 4 = Sorted by Data in KB's
/
/ To make alter the sort change the number after Order by in the last line of the script.
*/
SELECT
[Table Name] = OBJECT_NAME (p.object_id)
,[Row Count] = sub.[rowCount]
,[Total Reserved (KB)] = sum(reserved_page_count) * 8
,[Data (KB)] = sub.pages * 8
,[Index (KB)] = CASE WHEN sum (used_page_count) > sub.pages THEN (sum (used_page_count) - sub.pages) * 8 ELSE 0 END
,[Unused (KB)] = CASE WHEN sub.reservedpages > sub.usedpages THEN (sub.reservedpages - sub.usedpages) * 8 ELSE 0 END
,[Type] = ISNULL (CASE CAST(dc.[ClassType] AS INT) WHEN '0' THEN 'Inventory' WHEN '1' THEN 'Event' WHEN '2' THEN 'Common' END, 'Platform')
,[Solution] = ISNULL (vp.Name, 'Not Assigned')
FROM sysobjects so
JOIN sys.dm_db_partition_stats p ON p.object_id = so.id
JOIN (
SELECT
id = OBJECT_ID
,reservedpages = SUM (reserved_page_count)
,usedpages = SUM (used_page_count)
,pages = SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count END)
,[rowCount] = SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
FROM sys.dm_db_partition_stats
WHERE [row_count] > 0
GROUP BY object_id ) sub ON sub.id = p.object_id
LEFT JOIN DataClass dc ON dc.DataTableName = so.name
LEFT JOIN vItem it ON it.Guid = dc.Guid
LEFT JOIN vProduct vp ON vp.Guid = it.ProductGuid
WHERE so.type = 'U'
GROUP BY p.object_id, sub.usedpages, sub.reservedpages ,sub.pages ,sub.[rowCount] ,sub.usedpages ,dc.[ClassType], vp.Name
ORDER BY 1
Note: The above script has some potential minor limitations, currently none exist in the CMS suite but could arise. The following removes those limitations, however it does not list the solution or table type and may execute slightly slower.
--set up table variable for space used output
declare @tablesize table (name nvarchar(200), [rows] nvarchar(100), reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))
--insert space used data into table variable
insert into @tablesize
exec sp_MSforeachtable @command1 = "sp_spaceused '?'"
select *
from @tablesize
order by 3 desc
SQL script for Notification Server 6.0 SP2
Run the following query to manually purge the tables from the specified Solution GUIDs. In this case you will be purging any data older than 90 days from the Notification Server and Resource Management related tables. You can change the time value if desired. Change the limit on getdate()-90 on the query below to whatever time you need. Run it in SQL Query Analyzer against the Altiris database.
SQL Purge script (the time is set for 90 day out to purge entries older than that time)
-- The Guid being passed to the stored procedure spPurgeResourceEvent is a product guid.
declare @Time datetime
declare @Count int
set @Time = getdate()-90
set @Count = 100000delete from SavedReport where CreatedDate < @Time
-- Notification Server
exec spPurgeResourceEvent 'D0E33520-C160-11D2-8612-00104B74A9DF', @Count, @Time
-- Resource Management
exec spPurgeResourceEvent 'E81A4114-5D09-45DC-97F6-4B06F08C9AB0', @Count, @Time
SQL script for Notification Server 6.0 SP3
The data that the stored procedure (spPurgeResourceEvent) uses, is in the DataClass table and does not have parameters you set as in the SP2 version. The purge settings are found on the Notification Server console under the Configuration tab > Configuration > Server Settings > Notification Server Settings > Purging Maintenance > Resource Event Data Purge Settings sub-tab.
The following query can be used to manually initiate the event purging process on Notification Server 6.0 SP3 servers. Running the stored procedure within SQL Query Analyzer will avoid encountering the SQL timeout limitation that is imposed on all Notification Server processes. A one time manual cleanup will be sufficent to allow the nightly automated purging to work correctly.
-- Purge the event tables
execute spPurgeResourceEvent
Notes:
select
[Table Name] = so.[name]
,[DataClass Table Name] = isnull(dc.[name],'')
from sysobjects so
left join dataclass dc on object_id(dc.[DataTableName]) = object_id(so.[Name])
where so.[type] = 'U'
order by so.[name]