• Adam Thurgar

Weekly monitoring automation


For a medium sized law firm I was asked to take over the weekly database monitoring for 16 production SQL Servers. The person who ran these checks was a sysadmin going on 3 months leave and this firm had no DBA. Ideally this firm should have a monitoring tool in place (Redgate, Idera, SQL Sentry, Quest or Solarwinds) and they should have at least an accidental DBA who can keep an eye on everything. In reality they have none of this and all they wanted was to keep the lights on.

There was one script for checking the servers. It checked the log shipping status and thresholds, database status, SQL Server Error log checks (SSPI handshake failures, deadlocks, login failures and any other errors) and SQL Server Agent job failures. To make the whole process easier, quicker and less error prone I turned the script into a stored procedure that would output all the information from the script into a temporary table. We could then query the temporary table and only return information that was relevant (e.g. if there were no login failures - we would not report any login failures). Therefore the running of the checks was simpler as it was now just executing a stored proc and the results were quicker and easier to check as the information returned was more succinct. Hopefully it will save them time in the future.


5 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t