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.
Tag: trace flag
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?
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 .
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.
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.