When you feel the size of your SQL Database growing larger, you need to find the reason for what are the tables taking more storage memory space in your database. I am also came through the same situation in my customer end, because he is using SQL Server 2008 R2 Express. As you know, this is a free version, so it will allow only 20 GB storage size for every single database. I have got the following T-SQL query to list the size of all the tables in a sql databse and I have modified the query to list larger size tables first by ORDER BY statement, so that we can easily identify which tables are taking high storage memory size.
Thanks,
Morgan
Software Developer
T-SQL: List Used and Unused Size for all Tables
USE [MorganDB]; GO SELECT t.NAME AS TableName, p.rows AS RowsCount, 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.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 LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_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 UsedSpaceKB Desc
Output: List total space, used size and unused size for all tables
SQL Server query to get Complete Database size
You can use the built in stored procedure sp_spaceused to get the total space used by a SQL Database.USE [MorganDB]; GO exec sp_spaceused
Output: Returns total Database size
Thanks,
Morgan
Software Developer
No comments:
Post a Comment