table and index size calculation

contactjimmyus · May 12, 2009

I am optimizing the index and space utilization
I have run sp_space used on a table and the output is attached
Name Rows reserved Data Index size Unused
RskCalB_Invoices_Save 43501427 26325208 KB 4704472 KB 2746120 KB 18874616 KB
I need an interpretation of the out put .Is it meens that the total size of the table is 4.7 gb including 2.6 gb of index size ?What is ment by reserved and unused represents in the output...
Thanks a lot

MohammedU · May 12, 2009

Data + Index size + Unused = 26325208
4704472 + 2746120 + 18874616 = 26325208
Reserved - amount of total reserved space for the table
Data - amount of space used by the data for the table
Index_Size - amount of space used by the table's indexes
Unused - amount of usused space in the table

contactjimmyus · May 13, 2009

So as per the data 26 gb is allocated for this table and 18 gb is not used ,the actual data and the index comes to 6 gb.The allocated space will add up to the database file size

Sandy · May 14, 2009

Adding to All, You can also check this as references.Thanks,
Sandy.

SET NOCOUNT ON DECLARE
@cmdstr varchar(100)DECLARE @Sort bit SELECT
@Sort = 0 --Create Temporary TableCREATE TABLE #TempTable
( [Table_Name] varchar(50),Row_Count
int,Table_Size
varchar(50),Data_Space_Used
varchar(50),Index_Space_Used
varchar(50),Unused_Space
varchar(50)
)--Create Stored Procedure StringSELECT
@cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''--Populate Tempoary TableINSERT
INTO #TempTable EXEC(@cmdstr)--Determine sorting methodIF @Sort = 0 BEGIN
--Retrieve Table Data and Sort AlphabeticallySELECT * FROM #TempTable ORDER BY Table_Name END ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT * FROM #TempTable ORDER BY Table_Size DESC
END--Delete Temporay TableDROP
TABLE #TempTable

Log in or Sign up

table and index size calculation

contactjimmyus Member

MohammedU New Member

contactjimmyus Member

Sandy New Member

Share This Page