Tag Archives: DBCC

Important Trace Flags for DBA

A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server.

You can enable the trace flag from Startup Parameters, Session and Global Session level.

DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global

Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading
Trace Flag 4199 : enable query optimizer fixes
Trace Flag 1222 : writes the deadlock information in XML format into error log
Trace flag 835 : Enable the Lock Pages in memory for standard edition
Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects
Trace flag 1204 : Write information about the deadlock in text format
Trace flag 1211 : Disable the lock escalation based on the memory pressure
Trace flag 3226 : Prevents successful backup operation being logged
Trace flag 3014 : Returns more information to error log about backup operation
Trace flag 3502 : Write information about the checkpoint in the error log
Trace flag 3505 : Disables automatic checkpoint
Trace flag 3004 : Returns more information about the instant file initialization
Trace flag 1806 : Disables the instant file initialization

DBCC Config

Dbcc Config is an undocumented DBCC command which returns SQL server configuration. Configuration details are stored in Master database file 1 and on page 10.You have to enable the trace flag 3604 to get the result.

dbcc traceon(3604)
dbcc config
dbcc page(master,1,10, 3) 
CONFIG:


DS_CONFIG @0x032D6060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 8                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



PAGE: (1:10)


BUFFER:


BUF @0x043659F8

bpage = 0x069A4000                   bhash = 0x00000000                   bpageno = (1:10)
bdbid = 1                            breferences = 0                      bUse1 = 45452
bstat = 0x2c00009                    blog = 0x43212159                    bnext = 0x00000000

PAGE HEADER:


Page @0x069A4000

m_pageId = (1:10)                    m_headerVersion = 1                  m_type = 14
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99     m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0            Metadata: IndexId = 0                Metadata: ObjectId = 99
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 1                        m_freeCnt = 0                        m_freeData = 8190
m_reservedCnt = 0                    m_lsn = (0:0:1)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = -1715479820

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =                  Record Size = 9

Memory Dump @0x6300A060

00000000:   00000800 fb060000 08†††††††††††††††††....û....                

DS_CONFIG @0x6300A060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 0                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787


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