4

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

  • Did you had chance to read sqlskills.com/blogs/paul/… and I would first ask you to apply SP4 for 2008 – Shanky Jun 3 '15 at 12:46
  • I have yeah. I don't think it really helps in this case. "It’s supposed to be a very conservative estimate, but certain pathological cases can trip it up as it can’t account for all possible schemas." So if it does trip up is there another way? – hpk89 Jun 3 '15 at 12:52
  • IIRC there was bug in SQL Server where it used to do wrong estimation for checkdb I strongly suggest you to aply Sp4 and do estimation again. I am not aware of alternatives – Shanky Jun 3 '15 at 13:00
3

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

  • Perhaps add a suggested ORDER BY clause. – RLF Jun 3 '15 at 13:20
  • Thanks for this. I shall implement the same or similar and see what the growth looks like – hpk89 Jun 4 '15 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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