locked
unused space in large table RRS feed

  • Question

  • Hi All,

    I have partitioned table with lot of unused space in OLTP environment.   i ran following query to find fragmentation percent. Most of the values are around 1.5.  The fill factor for the table is set to 0 and the table has primary key.

    How can i find what's causing the unused space to grow and also

    free up the space without causing production issues.

    TIA

    -- Find the average fragmentation percentage of all indexes  
     SELECT a.index_id, name, avg_fragmentation_in_percent  
    FROM sys.dm_db_index_physical_stats (DB_ID(N'ProdDb'), 
          OBJECT_ID(N'dbo.transaction'), NULL, NULL, NULL) AS a  
        JOIN sys.indexes AS b 
          ON a.object_id = b.object_id AND a.index_id = b.index_id;   





     name rows reserved data index_size unused 
     Transaction         7,222,185,836 595439200 KB 421631304 KB 2082904 KB 171724992 KB 


    Thanks



    • Edited by GKS001 Wednesday, June 20, 2018 2:07 PM
    Wednesday, June 20, 2018 2:06 PM

Answers

  • Reserved = data+index_size+unused 

    As you can see the unused size is 171724992 , Its as usual there is no need to worry with the size , seems that the pages might have been fragmented (might be due to Insert, Update and Delete(DML)) due to which the unused is showing such values, please check the fragmentation.  

     
    • Marked as answer by GKS001 Wednesday, June 20, 2018 2:53 PM
    Wednesday, June 20, 2018 2:40 PM

All replies

  • Please run below statement to get the tables space details:

    sp_spaceused 'tablename'


    Wednesday, June 20, 2018 2:10 PM
  • Thanks! Please see below.

    Wednesday, June 20, 2018 2:24 PM
  • Reserved = data+index_size+unused 

    As you can see the unused size is 171724992 , Its as usual there is no need to worry with the size , seems that the pages might have been fragmented (might be due to Insert, Update and Delete(DML)) due to which the unused is showing such values, please check the fragmentation.  

     
    • Marked as answer by GKS001 Wednesday, June 20, 2018 2:53 PM
    Wednesday, June 20, 2018 2:40 PM