Which tables are consuming the most space in the Automation Suite database

News Knowledge Base
1

How to check which tables are consuming the most space in your AutomationSuite_ database?

To check the tables occupying the most resources in an MS SQL Server database, use the following SQL query. This query will return the top 10 tables in the AutomationSuite_Platform database that consume the most space.

Note: Feel free to update the DB name below as per the requirement:

USE AutomationSuite_Platform;

SELECT 

   TOP 10 

   s.name AS schema_name,

   t.name AS table_name,

   p.rows AS row_count,

   SUM(a.total_pages) * 8 AS total_size_kb,

   SUM(a.used_pages) * 8 AS used_size_kb,

   SUM(a.data_pages) * 8 AS data_size_kb

FROM 

   sys.tables t

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

INNER JOIN 

   sys.schemas s ON t.schema_id = s.schema_id

WHERE 

   t.type = 'U'

GROUP BY 

   t.name, s.name, p.rows

ORDER BY 

   total_size_kb DESC;



Expected output:

image.png