Determining which tables are largest or growing fastest in the Silk Central Database
As you expand your database and add executions and data you will begin to see a steady rise in the size of the database. To be able to manage a lot of the information better it is some times useful to see where the expansion of the database is occurring.
The following query will do this. It outputs each table in the Silk Central database, and it's current size.This report can be run directly in Silk Central, using the advanced report. Therefore, so for users who manage this information it is something they will be able to see and compare everyday.
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 UnusedSpaceKB
sys.schemas s ON s.schema_id = t.schema_id
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows
This will give an indication of at what is causing the increase in space and how to better manage this moving forward.