Tag Archives: trace flag

Day 14–Trace Flag 3505–Control SQL Server Checkpoint Behavior

What’s is checkpoint? Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from buffer cache to disk. It helps in recovery time of the database so that when the database start’s up there is no need to do this for all dirty pages, so checkpoint happens in an regular interval. For more information on checkpoint refer the KB article http://msdn.microsoft.com/en-us/library/ms188748.aspx.

Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition

Dedicated administrator is one of the new feature introduced from SQL Server 2005. This is a wonderful feature for DBA’s to troubleshoot SQL Server when it goes unresponsive. For more on DAC check the article by Deepak. However this feature is not supported in SQL Express edition by default. So how can I utilize this feature in SQL Express edition?

Continue reading Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition

Day 12–Trace Flag 1222–Capture Deadlock information in XML like format

Yesterday we saw the trace flag 1204 which will give you the information based on nodes. Today we are going to get similar deadlock information using trace flag 1222 in XML like format which means the format does not conform to an XML Schema Definition (XSD) schema. After you enable the trace flag when an deadlock happens it will pushes the information to errorlog you can get the deadlock information from errorlog .

Continue reading Day 12–Trace Flag 1222–Capture Deadlock information in XML like format

Day 11–Trace Flag 1204– Capture Deadlock information focused on nodes

Have you ever wonder to capture Deadlock information? If so we have wonderful trace flags available in SQL server which will give you bunch of information about deadlock information. Today we are going to see one of the trace flag 1204 which will capture deadlock information focused on two nodes that is involved in deadlock.

Continue reading Day 11–Trace Flag 1204– Capture Deadlock information focused on nodes

Day 10–Trace Flag 4022–Bypass startup procedures in SQL Server

Do you have startup procedures? In some scenarios we might be having some startup procedures in our SQL Server system and we might face some issue during starting SQL server. So for troubleshooting purpose we might need to bypass the startup procedures so that SQL Server don’t need to grant worker thread for each procedure.

Continue reading Day 10–Trace Flag 4022–Bypass startup procedures in SQL Server