Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered indexes, implementing row or page compression, etc.

However, there are a few cases where I was able to reclaim even more space by executing DBCC CLEANTABLE. With hundreds of databases in my environment, it's not possible to know what users do in each one and it's completely acceptable that there will be changes involving the dropping of fixed length columns. I've typically found these opportunities by looking at row counts vs. page counts in some object space utilization scripts I've written. I'd like to take this a step further by attempting to automate the detection of these sorts of scenarios.

What I'd like to know is if anyone out there is actively monitoring for these sorts of opportunities and if so, what do you look for specifically?

My thoughts were to write something along the lines of gathering the max and min size of a row, the number of rows in the table, the number of pages allocated, and the number of pages used, then doing some basic math to log results that are well outside what would be "expected".

share|improve this question
I highly recommend using the batch_size parameter on large tables. This will cause the command to run in a series of transactions as opposed to one giant transaction. –  datagod Jun 1 at 14:50

1 Answer 1

up vote 9 down vote accepted

The solution I'd think of for this problem is to run weekly a job that will run sp_spaceused for all tables in a database and save this data in a table. If there are differences in size for each table greater than..let's say..10%, I would run the dbcc cleantable.

My code to loop through table sizes looks like that:

if OBJECT_ID ('tempdb.dbo.#temp') is not null
    drop table #temp;

if OBJECT_ID ('dbo.BigTables') is not null
    drop table dbo.BigTables;

CREATE TABLE [dbo].[BigTables] (
    [table_name] [sysname] NOT NULL,
    [row_count] [int] NULL,
    [col_count] [int] NULL,
    [data_size] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [DBName] [nvarchar](128) NULL

    table_name sysname ,
    row_count int,
    reserved_size varchar(50),
    data_size varchar(50),
    index_size varchar(50),
    unused_size varchar(50)

INSERT     #temp
EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables
SELECT     a.table_name,
           count(*) as col_count,
           getdate() as [Date],
    'MY DB' as DBName
FROM       #temp a
INNER JOIN information_schema.columns b
           ON a.table_name collate database_default
                = b.table_name collate database_default
GROUP BY   a.table_name, a.row_count, a.data_size
ORDER BY   CAST(Replace(a.data_size, ' KB', '') as integer) desc


Select * from dbo.BigTables;

Now you only need to build the logic that will verify what would be the size change through the week and schedule it.

share|improve this answer
Interesting... I hadn't even considered sp_spaceused!! Thanks for this Marian, I think that's just what I was looking for. –  AndrewSQL Jan 19 '11 at 1:29
Now, if there are reasons to suspect inaccuracies in the table size reports, you should go for DBCC UPDATEUSAGE (this will fix the row and page counts). Glad to be of help! –  Marian Jan 19 '11 at 8:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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