Sql-server – Should you run DBCC CHECKCONSTRAINTS regularly

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.

When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:

1. Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
2. Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.

Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.