How do I determine what the database table sizes are per solution?

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

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 = 100000

delete 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:

• An importable Notification Server table space report is available in article 30567, "How can I get a report that determines the database table sizes per solution?"
  • The report has two display modes: Table Size and Table Name.
  • It can filter by Solution.
• Not all tables are listed in the Dataclass table. The following SQL script will show you what is not in the Dataclass table

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]

• When purging large tables, your transaction log can grow quite large, until the transaction is completed.