Take the 2-minute tour ×
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

1 Answer 1

up vote 7 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;

go

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
)

GO

CREATE TABLE #temp (

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

)

go

INSERT     #temp

EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables

SELECT     a.table_name,
           a.row_count,
           count(*) as col_count,
           a.data_size,
           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

DROP TABLE #temp

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. I would gladly help you with that, if you need.

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

 
discard

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.