DBCC CHECKDB WITH WITH ESTIMATEONLY on MASTER Results 4 Rows

Hi All,

Today while working on something I figure out something. When we run DBCC CHECKDB (master) WITH ESTIMATEONLY the result set has 4 rows. Why?

Command:

DBCC CHECKDB (MASTER) WITH ESTIMATEONLY

Output:

Estimated TEMPDB space needed for CHECKALLOC (KB)

-------------------------------------------------
240

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
668

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
521

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
14535

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So in the above output we are seeing that there 2 entries for CHECKALLOC and CHECKTABLE.

WHY?

Anser is: The second entry is for RESOURCE DATABASE.

Try running DBCC CHECKDB on MASTER and in the output you will see that there will be 2 outputs (One for MASTER db and other for RESOURCE db). Even as per books online:

when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL_ONLY or ESTIMATEONLY option is set.



 
Regards
Gurpreet Singh Sethi

Missing Tables in Master Database

Hi All,

I met with a strange incident where some tables from MASTER database were deleted. Below are the steps which I take in order to correct this problem.


Problem -

--Tables from MASTER database are dropped (4-5 tables).

--User databases are not working fine.

--When they do SP_SPACEUSE it gives error about a table

SPT_VALUE table is not there

--Customer don't have database backups.

--Customer believes they have recently moved to production.

Environment -


SQL Server 2005 Enterprise Edition x32 SP2 3054
Windows 2003 Enterprise Server x32 SP2

Root Cause (if known) -

--System tables from master databases were deleted.

Resolution -

--We have below tables missing from Master Database.

MSreplication_options
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values

--As system tables from master databases were deleted so we were left with 2 options.

1) Try to rebuild system database.
2) Reinstall new instance of SQL and move user database.

--We select 1st option.

--As we have couple of logins in the databases so we script out those logins using KB article. http://support.microsoft.com/kb/918992

--We script or a job as well although that was disabled.

--We used below command to rebuild master databases but it was failing with errors.

setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=sql.server SKUUPGRADE=1 SQLINSTALLDIR="E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA"

Last Action : InstallFinalize
Error String : An installation package for the product Microsoft SQL Server 2005
cannot be found. Try the installation again using a valid copy of the
installation package 'SqlRun_SQL.msi'.
Error Number : 1706

--We checked into the registry keys and changed LastUsedSource key to new location (from where we were staring the installation).

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\LastUsedSource

--Again start installation but it again fails with same error.

--Further analyzed and found that we didn’t have a key named NET under above mentioned registry key so we created this new key and add a key in it with the name 1 having
type Expandable String. This points to new location of setup files as was for LasteUsedSource.

--Now we have below keys in registry.

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList – With keys
LastUsedSource
PackageName
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\Media – With keys
1
DiskPrompt
MediaPackage
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\Net – With keys
1

--Start rebuilding task and it completes successfully.

--We attached user databases and recreated logins (from the script created) & recreated job.

--We installed 3054 hot fix successfully.

Regards
Gursethi