Log Shipping – NORECOVERY vs. STANDBY mode

There are two ways to find out the table size;

first option will be to run the system stored procedure “sp_spaceUsed”

  1: EXEC sp_SpaceUsed 'table_name'

Option 2 uses the dmv sys.dm_db_partition_stats

  1: SELECT  t.schema_name + '.' + t.table_name AS table_name
  2:        ,t.index_name
  3:        ,SUM(t.used) AS used_in_kb
  4:        ,SUM(t.reserved) AS reserved_in_kb
  5:        ,SUM(t.tbl_rows) AS rows
  6: FROM    ( SELECT    s.Name schema_name
  7:                    ,o.Name table_name
  8:                    ,COALESCE(i.Name, 'HEAP') index_name
  9:                    ,p.used_page_count * 8 used
 10:                    ,p.reserved_page_count * 8 reserved
 11:                    ,p.row_count ind_rows
 12:                    ,CASE WHEN i.index_id IN ( 0, 1 ) THEN p.row_count
 13:                          ELSE 0
 14:                     END tbl_rows
 15:           FROM      sys.dm_db_partition_stats p
 16:           INNER JOIN sys.objects AS o ON o.object_id = p.object_id
 17:           INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
 18:           LEFT OUTER JOIN sys.indexes AS i ON i.object_id = p.object_id
 19:                                               AND i.index_id = p.index_id
 20:           WHERE     o.type_desc = 'USER_TABLE'
 21:                     AND o.is_ms_shipped = 0 ) AS t
 22: GROUP BY t.schema_name
 23:        ,t.table_name
 24:        ,t.index_name
 25: ORDER BY 5 DESC
 26: 
 27: