DBCC CHECKDB Estimation Alternative

dbcctempdb

I am in the process of rolling out DBCC CHECKDB across our instances.

Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY.

The way I have been gauging whether a FULL CHECKDB should be done, or a PHYSICAL_ONLY is by examining the results from DBCC CHECKDB WITH ESTIMATEONLY.

However I ran this yesterday on SQL Server 2008 (SP1) and the tempdb estimation for a certain database was 510MB.

However in practice it filled the entire 19GB drive so the estimation was way off!

I was under the impression the ESTIMATEONLY bug was in SQL 2008 R2, not the standard 2008 ?

Is there another way to gauge tempdb usage? Some sort of database size to tempdb usage ratio: (I.e if I have a 500GB database how much tempdb consumption should I be expecting?)

I also need to implement test restores because we don't do that here. Once I have that server in place I will switch to PHYSICAL_ONLY for everything then do a full CHECKDB on the restored database.

Thank you

Best Answer

What we do to estimate the usage of tempdb and its size is to track its growth via data tracked in tables for a period of time before reboot happens as a part of maintenance activity.

P.S note: It may not be an alternative, but helps in tracking how tempdb grows over a period of time

Step 1 Create table to store the data:

CREATE TABLE [dbo].[TempDBFileSize] (
[TFSID] [int] IDENTITY (1, 1) NOT NULL ,
[FileID] [int] NULL ,
[File_Logical_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Default_File_SizeMB] [int] NULL ,
[ActualKB] [int] NULL ,
[ActualMB] [int] NULL ,
[File_MaxSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File_Growth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Growth_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Physical_File_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCaptured] [datetime] NULL 
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD 
CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED 
(
[TFSID]
) ON [PRIMARY] 
GO

Step 2 Create SP to catch the tempdb data and log file usage within above created table:

CREATE PROCEDURE dbo.spTempdbFileSize
AS
/*
------------------------------------------------------
-- Object Name: dbo.spTempdbFileSize
-- Project: SQL Server TempDB Sizing
-- Business Process: SQL Server Capacity Planning
-- Purpose: Capture the TempDB growth
-- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files
-- Database: TempDB
-- Dependent Objects: dbo.TempDBFileSize
-- Called By: Admin - TempDB Sizing
-- Upstream Systems: None
-- Downstream Systems: None
-- 
------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
------------------------------------------------------
-- 001 | N\A | 12.02.2007 | Edgewood | Original code
-- 002 |     | 03.30.2009 | Penberth | Modified the code to get the actual size
--                                     of the tempdb alongside the the default sizes.
--                                     Added the ActualKB and ActualMB columns and 
--                                     renamed the [File_SizeMB] to [Default_File_SizeMB]
--
*/
SET NOCOUNT ON
INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc,
[Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type,
Physical_File_Name, DateCaptured)
SELECT File_ID,
MasterTbl.[Name],
MasterTbl.State_Desc,
MasterTbl.Type_Desc,
(MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB',
(TempTbl.[size]*8) AS ActualKB, 
(TempTbl.[size]*8)/1024 as ActualMB,
File_MaxSize = CASE 
WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth'
WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax'
ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10)) END,
File_Growth = CASE 
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0 
THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10))
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1 
THEN CAST(MasterTbl.[Growth] AS varchar(10))
ELSE 'Unknown' END,
Growth_Type = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes'
WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage'
ELSE 'Unknown' END,
MasterTbl.[Physical_Name],
GETDATE() AS 'DateCaptured'
FROM Master.sys.master_files MasterTbl (NOLOCK) 
LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK) 
 ON MasterTbl.[Physical_Name] = TempTbl.[filename]
WHERE Database_ID = 2
SET NOCOUNT OFF
GO

Step 3: Execute the above SP via SQL agent job: we schedule it to run every 4 hours. Therefore by the end of the week we have enough data to predict how much tempdb is growing and thus estimating accordingly.

Just execute 

    SELECT * 
FROM [dbo].[TempDBFileSize]
ORDER BY ActualMB

to view the stored results

Related Question