Sql-server – Table Size Analysis on SQL Server 2000

sql-server-2000

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

How can we find out which objects are causing this massive NULL space usage?
It seems that approx 26GB are "reserved" for NULL, 16GB for BigTable – is this basically a waste of space or are real records involved?

Best Answer

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:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            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
GROUP BY Item, Color WITH ROLLUP

Output:

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

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment. So you can track status before and after

Sql-server – Changing SQL Server 2000 usernames

Some notes

When you create a login, then the name must match AD exactly
EXEC sp_grantlogin 'DOMAIN\Andy Soell
When you map the login to a database user, it can be renamed
EXEC sp_grantdbaccess 'DOMAIN\Andy Soell', 'Dilbert'

The 2nd bit will fix your issue with implied schema. This happens if don't qualify schema (eg dbo) because SQL Server 2000 has pretty much no user-schema separation like later versions

Note: you can't rename users in SQL Server 2000 IIRC: need to run sp_revokedbaccess then sp_grantdbaccess. If you own objects because of your implied schema then you'll need sp_changeobjectowner. I think SQL EM will prompt you.

That is, you should always qualify schema in SQL Server.

CREATE TABLE dbo.viewname...

SELECT * FROM dbo.myTable

EXEC dbo.MyProc

Then no need to change the users

Best Answer

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

Sql-server – Changing SQL Server 2000 usernames

Related Question