Web Listing 3: mnt_DBCC_VLDB.txt
USE [msdb]
GO
IF object_id('dbo.mnt_DBCC_VLDB') IS NULL BEGIN
EXEC('CREATE PROCEDURE dbo.mnt_DBCC_VLDB AS RETURN 0')
END
GO
ALTER PROCEDURE dbo.mnt_DBCC_VLDB
@days tinyint = 7,
@db sysname,
@version smallint = 2008,
@results nvarchar(MAX) OUTPUT
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
/***************************************************************************
Stored Procedure: mnt_DBCC_VLDB
Written by: David Paul Giroux
Date: 04/2009
Purpose: Produces DBCC CHECKTABLE script
Input Parameters: @days tinyint, @db sysname
Output Parameters: @results nvarchar(MAX)
Called By: msdb.dbo.mnt_DBCC
This scproc can be called directly but is designed to be called by msdb.dbo.mnt_DBCC.
Example Usage if called directly:
DECLARE @results nvarchar(MAX)
EXEC msdb.dbo.mnt_DBCC_VLDB
@days = 7,
@db = N'AdventureWorks',
@version = 2008,
@results = @results OUTPUT
SELECT @Results
Calls: None
Data Modifications: Updates SQL Server Agent Job: Maintenance_DBCC_CHECKDB
Uses: WITH ALL_ERRORMSGS, NO_INFOMSGS;
The sproc calculates the size of each table and then spreads the weight into
a number of groups based on @days. This allows for an even daily load (close as possible)
A different group is returned each day.
This script knows on any given day which set of tables to execute because of the following statement:
WHERE VLDB_Group = DATEDIFF(dd, N'01-01-2009', GETDATE()) % @days
***************************************************************************/
DECLARE @cmd nvarchar(700)
DECLARE @crlf nchar(2)
SET @crlf = NCHAR(13) + NCHAR(10)
-- All tables
DECLARE @Pool TABLE (
[Name] sysname,
ObjectID int primary key,
ReservedPC bigint
)
-- Info for tables with XML or Fulltext Indexes
DECLARE @Others TABLE (
ObjectID int primary key,
ReservedPC bigint
)
-- Tables with final ReservedPC amount and grouped by @days
DECLARE @Final TABLE (
VLDB_Group tinyint,
[name] sysname primary key,
ReservedPC bigint
)
IF @version = 2008
BEGIN
-- User Tables, System Base Table, Indexed Views, Internal Tables
SELECT @cmd =
N'USE [' + @db + N']' + @crlf +
N'SELECT SCHEMA_NAME(o.schema_id) + N''.'' + o.name, ' + @crlf +
N' o.object_id, SUM(ps.reserved_page_count)' + @crlf +
N'FROM [' + @db + N'].sys.objects o WITH (NOLOCK)' + @crlf +
N'JOIN [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +
N'ON o.object_id = ps.object_id' + @crlf +
N'WHERE o.[type] IN (N''U'', N''S'', N''V'', N''IT'')' + @crlf +
N'GROUP BY SCHEMA_NAME(o.schema_id) + N''.'' + o.name, o.object_id'
END
ELSE BEGIN
-- User Tables, Indexed Views, Internal Tables
SELECT @cmd =
N'USE [' + @db + N']' + @crlf +
N'SELECT SCHEMA_NAME(o.schema_id) + N''.'' + o.name, ' + @crlf +
N' o.object_id, SUM(ps.reserved_page_count)' + @crlf +
N'FROM [' + @db + N'].sys.objects o WITH (NOLOCK)' + @crlf +
N'JOIN [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +
N'ON o.object_id = ps.object_id' + @crlf +
N'WHERE o.[type] IN (N''U'', N''V'', N''IT'')' + @crlf +
N'GROUP BY SCHEMA_NAME(o.schema_id) + N''.'' + o.name, o.object_id'
END
INSERT INTO @Pool
EXEC (@cmd)
-- Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
-- Row counts in these internal tables don't contribute towards row count of original table.
SELECT @cmd =
N'USE [' + @db + N']' + @crlf +
N'SELECT it.object_id, sum(ps.reserved_page_count)' + @crlf +
N'FROM [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +
N'JOIN [' + @db + N'].sys.internal_tables it WITH (NOLOCK)' + @crlf +
N'ON ps.object_id = it.object_id' + @crlf +
N'WHERE it.internal_type IN (202,204)' + @crlf +
N'GROUP BY it.object_id'
INSERT INTO @Others
EXEC (@cmd)
UPDATE @Pool
SET ReservedPC = a.ReservedPC + b.ReservedPC
FROM @Pool a
JOIN @Others b
ON a.ObjectID = b.ObjectID
-- This additional table is needed because cannot filter on ROW_NUMBER function
INSERT @Final
SELECT ROW_NUMBER() OVER(ORDER BY ReservedPC DESC) % @days,
[name],
ReservedPC * 8
FROM @Pool
-- Final results filtered by VLDB_Group
-- The VLDB_Group changes daily
SET @results = N''
SELECT @results = @results + N'DBCC CHECKTABLE ([' + @db + N'.' + [name] + N']) WITH ALL_ERRORMSGS, NO_INFOMSGS;' + @crlf
FROM @Final
WHERE VLDB_Group = DATEDIFF(dd, N'01-01-2009', GETDATE()) % @days -- 01-01-2009 is arbitrary
SET @results =
N'DBCC CHECKALLOC ([' + @db + N']) WITH ALL_ERRORMSGS, NO_INFOMSGS;' + @crlf +
N'DBCC CHECKCATALOG ([' + @db + N']) WITH NO_INFOMSGS;' + @crlf + @crlf +
@results
GO