Our SQL Server 2000 database .mdf file is 27Gb large which seems much larger than is plausible. Using the following query we tried to analyse table sizes:

select cast(object_name(id) as varchar(50)) AS name,
    sum(CASE WHEN indid<2 THEN rows END) AS rows,
    sum(reserved)*8 AS reserved,
    sum(dpages)*8 AS data,
    sum(used-dpages)*8 AS index_size,
    sum(reserved-used)*8 AS unused
from sysindexes with (nolock)
    where indid in(0,1,255) and id>100 
    GROUP BY id with rollup
    ORDER BY sum(reserved)*8 desc

The results were as follows:

Name           Rows       Reserved  Data     Index_Size  Unused
NULL           15274279   26645456  5674592  17361464    3609400
BigTable         875966   16789712   471096  13349816    2968800
  1. How can we find out which objects are causing this massive NULL space usage?
  2. It seems that approx 26GB are "reserved" for NULL, 16GB for BigTable - is this basically a waste of space or are real records involved?
  • 1
    when did you last defragment? which service pack? Do you regularly run DBCC CHECKDB? – Mitch Wheat Mar 8 '13 at 9:54
  • 3
    So what does select id, reserved from sysindexes order by reserved desc; yield? Also BigTable seems to have 13 GB worth of indexes, so I wonder about your definition of "waste of space"... – Aaron Bertrand Mar 8 '13 at 13:32
  • For a table of 470 MB you have 13.3 GB of indexes? Do you have an index for every combination of the columns? – Marian Mar 8 '13 at 14:00
  • You mean a disk defrag? Didn't know that would have any influence on the size of a single file. AFAIK our servers are virtual and using SAN technology. – Marc Mar 8 '13 at 15:20
  • 1
    I suppose the server is not reporting the correct space used by the tables. You could use the system procedure sp_spaceused (for each table) to see the reported space and if numbers don't match with what you think they should be, then use DBCC UPDATEUSAGE (it will fix the row and page counts). Please read also answer1 and answer2. – Marian Mar 23 '13 at 11:46

You're not using WITH ROLLUP correctly. WTIH ROLLUP creates a subtotal and/or total based upon what you tell it. You didn't specify what to call the subtotal and/or total so it is calling it NULL.

This query generates a subtotal report:

            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory


Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00             
| improve this answer | |
  • This is neither helpful nor "correct". It is a matter of taste whether a rollup of a null value should display 'UKNOWN' or what-have-you (using CASE) and it's irrelevant to this question. – Marc Mar 26 '13 at 14:43
  • This is indeed the correct answer. They overlooked this important thing in the question. – Lorenz Meyer Nov 11 '16 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.