We recently worked with a client to troubleshoot issues with a SQL Server instance. The client was running SQL Server 2012 on a Virtual Machine. The applications running on the SQL Server were running into issues and the client was unsure what the root cause of the issues was.
Initially, we set up web application monitoring with the client to send alerts if the web application began running slower than normal, thus letting us know the next time a problem was encountered. By setting a timeout threshold for the page load speed, we were able to identify different time periods where the system appeared to have an issue accessing data from the SQL server. Using the built in alerts, we received emails as soon as an issue was detected.
Next we wanted to set up SQL Server monitoring, so we helped the client install the MetricsView Agent to gather Windows Performance Counter data from the SQL server. Once the MetricsView Agent was successfully installed we were able to gather any data that is reported by Windows Performance Counters including SQL Server usage, processor utilization, bandwidth usage, memory allocation and hard drive disk space availability.
As we monitored all of these metrics we set up maximum and minimum thresholds for each data set so that we would be alerted if any of the critical metrics exceeded the expected boundaries. What we found almost immediately was that it was not, in fact, a SQL issue at all, but a simple issue with the way the virtual server was created and the hard drives were partitioned.
This particular client had created two partitions on the drive, the first one for simply the operating system and the second drive was for data storage. What we quickly found was the OS drive still had some SQL logs on it that really should have been stored on the data drive, and the SQL logs were causing the drive to hit it’s capacity. The user had several options to resolve this issue. They could increase the partition of the virtual drive, depending on if it was set up as a dynamic drive, or they could change their sql server settings to store the logs on the data drive as well as set up automatic truncation of the logs after a specified period of time.
The fact that we were able to utilize multiple components of the Dotcom-Monitor suite of tools to help the client pinpoint the issue speaks volumes about the ability of Dotcom-Monitor to go above and beyond the simple uptime/downtime monitoring we started out performing over 15 years ago. The tools have truly evolved to contain troubleshooting tools and performance tweaking guides that help you gain a complete 360 degree view of how your infrastructure is performing and how it affects your websites and web applications.
Sign up now for a free 30 day trial to monitor web applications and web sites, from the hardware right up through the network to the actual responsiveness of SQL servers and each page load from dozens of locations around the world.