Seems like you have a serious database corruption as CHECKDB points it out
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
The only feasible options are :
- Restore from backup or
- Repair with data loss => Might leave the database in a transactionally inconsistent state.
In both cases, you will have data loss (provided you have good full and T-log backups before that guy pushed green button instead of blue).
I would go for option 1 => Restore from a know latest good backup !
Refer to IAM pages, IAM chains, and allocation units and IAM page corruption examples
UAT looks like as with success, no data missing
If you regularly have UAT in sync with PROD, best is to use that dataset to restore PROD - unless you are masking PROD data in UAT.
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
Best Answer
Actual serious although unusual suggestion: drop all the indexes in the database - or some of them - then "DBCC CHECKDB" has fewer things to check. Of course you can put them back after...
Alternatively, rebuilding indexes may produce a simpler arrangement of data for CHECKDB to analyse... this is actually a guess, but you may use fewer pages to store a table, at least.
https://technet.microsoft.com/en-us/library/ms176064(v=sql.105).aspx describes several WITH options which increase or decrease the workload of CHECKDB, and another option ESTIMATEONLY which causes it do skip any checking but tell you how much space in tempdb is required for checks. It's implied that this varies when you set other parameters with ESTIMATEONLY i.e. TABLOCK, PHYSICAL_ONLY. These do skip some checks.
Most but apparently not all of the checks in CHECKDB can be run as different commands, as described.
https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/ has performance-related suggestions including restoring the database backup to a different server to be checked. This too might not be a perfect check if the restore server is running a later SQL Server version and quietly "fixes" flaws of the original database during restore, so that you don't see them in the copy.