Determining which tables are largest or growing fastest in the Silk Central Database

0 Likes

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.

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 UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
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

This will give an indication of at what is causing the increase in space and how to better manage this moving forward.

Comment List
Related
Recommended