Procedure for when  DB block is observed:

1. Run the stored procedure sp_who on the database. This will look at all DB sessions and record what sessions are blocking.   Save the results as they will be needed to review sessions on the application server
2. Using these results, link the  user sessions on the app server to the db sessions by using the blocked_by  value in the sp_who results.
3. Investigate what the user was doing to when the block was created and report it to support.

Processes to Use to manage system and database proactively to aid in system stability:

Monitor Database For:

1. Fragmentation -  Particularly keep an eye on the application lock table (oq.app_lock).   Rebuild the table when fragmentation is greater than 30%.   Review all table fragmentation on a weekly basis and rebuild any index with fragmentation over 30%.  Have a formal defrag plan and routine.
2. Monitor Database Space -  Do not rely on SQL Servers auto grow functions.    Observe the growth rate of the database and manually grow it based on the growth rate.     Auto growth can be turned on after manually growing the DB, but it should be set to very small chunks of growth – 2-4 megs
3. Monitor the transaction log file size -   Make a determination on how big this will grow.  Pre-grow the log file size to the size determined. Auto growth can be used if the size to grow is set to 1-2MB.
4. Review Temp DB files -  There should be one temp db file per CPU on the database.

Monitor Database and Application Server for:

1. CPU Utilization
2. Memory Utilization
3. Monitor the time periods that CPU/memory utilization goes over 90%

Monitor Application Server for :

1. Paging