Detecting and reporting when a SQL Server database data or log file experienced a growth operation can be very useful for many reasons, such as:

• Analyzing and predicting disk space utilization on the server
• Detecting optimum values to setup for database growth
• Detecting any long running transactions that causes growth in log or data files.

In this post I present a simple method for querying information regarding database growth activity within a SQL Server instance, as well as setting up a simple email alert that gets triggered based on a configured growth threshold.

Read more →

Diagnosing CPU performance issues with SQL Server can be a bit tricky, particularly if you have a system that exhibit a CPU spikes pattern throughout the day. You could run some SQL DMVs in order to identify holistically how much CPU time each query is consuming, but that does not give you a real-time way of tracking CPU resource starvation on SQL Server.

Recently I have been asked to devise a system on a few of our servers that are experiencing CPU resource bottleneck issues, the monitoring system needs to identify when SQL Server is suffering from CPU bottlenecks, and report the queries currently running on the server via email alerts, along with performance statistics that can help you diagnose which queries are consuming most of the resources, and why are they doing so.
Read more →

Trying to figure-out why your log file for a particular database is not “naturally” shrinking is the first steps you need to take in order to diagnose and resolve a large SQL Server transaction log file.

Transaction Log files generally grow because there is something that is stopping SQL Server from reusing the already allocated space to this file, which will essentially cause the Transaction Log file to grow (as per the growth configuration settings) in order to keep the database operational. This could be caused by a natural increase in utilization for that database, or by a blockage (an active operation running too long, or waiting on mirroring to complete), so before deciding how to proceed with resolving a large Transaction Log file, it is highly recommended to figure out why did this happen.

Read more →