Monitoring SQL Server with dbWatch
If you are a database administrator dealing with SQL server, you are in good hands. Not only can dbWatch help you solve problems related to your hard drives connected to your database instances, but it can also help you solve problems relevant to the availability of your database instances, it can provide advice relevant to the performance and maintenance of your database instances and even guide you through the tunnels of replication.
Availability monitoring jobs, as the name suggests, monitor the availability of your database instances – meaning that if one or more of your database instances go down, you will be instantly alerted. Capacity monitoring jobs monitor the capacity (storage) parameters – those can include parameters associated with your hard drives, etc.
Here is what dbWatch can help you when monitoring SQL Server:
You can monitor availability:
dbWatch can help you maintain your SQL server instances if you so desire:
dbWatch can also help you keep an eye on performance and even clustering and replication if you so desire!
However, for the purposes in this blog post, we are only interested in how can dbWatch help you monitor your hard drive performance. To see the available options, simply expand the Capacity section:
You will see a bunch of jobs suited for the hard drives in your database instance: these jobs can help you check your disk capacity or disk space usage. Right click one of them, click Configure and you will see the options that can be set:
These options depict at what stage dbWatch will produce an alarm or a warning. The formatting might seem weird, but it is not – you just need to specify a disk drive and the size of the amount in free space in megabytes. “C:\=2000” in this case means that if the amount of free space on the C:\ drive will fall below 2GB, dbWatch will provide an alarm. The amount of free space can also be specified in percentages – in this case, if the amount of free space on the C:\ drive falls below five percent, dbWatch will return an alarm too.
In this case, you can also enable or disable the xp_cmdshell command – this command allows the running of commands from the SQL shell. Bear in mind that the use of this command can be dangerous (it allows to pass commands to be executed by the OS), so only enable it if you know what it does and you are sure you need it.
dbWatch can also provide you with alarms or warnings when your disk space falls below certain amount of megabytes – this job is self-explanatory:
Also, dbWatch has a specific job that can check the size of your data files – if the amount of free space in percent falls below the specified value, the job will return an alarm or a warning:
Here you can specify the value in percent when the alarm is triggered, what databases are ignored, you can define the amount of minimum free space you want to have left for your data files to grow and define a warning threshold – this job can be useful if you want to make sure your databases never run out of disk space when data files grow. If you are facing such an issue (or have faced such an issue in the past), make sure to keep an eye out on this job.
dbWatch can also check how much space are your temporary databases using – such a job can be useful if you want to make sure that your temporary database does not use more storage space than you want it to use. Since the role of a temporary database in SQL server is pretty significant (it’s used as a caching storage to store certain objects), it is a great idea to always keep an eye on it too.
To make sure your temporary databases are in shape, simply configure the temporary database space usage job: configure certain thresholds (you can also configure other things, for example, the number of hours your statistics will be kept in the history table) and you should be good to go!
As far as database space is concerned, transaction logs are also particularly important – dbWatch also has a procedure that checks the size of the transaction logs and compares it to the size of the database – that is the transaction log size check. This job can also be configured – you can set it to ignore certain databases, set the minimum size of the log files to be examined, set alarm or warning thresholds that will be triggered if the transaction log file size exceeds a specified percentage too:
dbWatch can also let you track the space usage of the transaction logs themselves – simply specify a maximum percentage of the space usage allowed in the transaction log before a warning should be triggered with the Transaction log space usage job:
To summarize, the SQL Server jobs available in dbWatch are really extensive – even though we did not dive deeper into everything that dbWatch can offer in this space (we only covered jobs relevant to hard drives that can help you make sure the disks that your SQL Server database instances are running are always in a good decent shape), this blog post should have provided you with some insight in what dbWatch can do. If you have any further questions or concerns, do not hesitate to reach out to the team – they will be glad to help you out.
About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.