Categories
Elasticsearch ELK Kibana Peoplesoft Perl SQL Server

Visualizing SQL Server Logspace Usage with Elasticsearch, Kibana and Perl

There are many ways possible ways to collect logspace usage data from SQL Server – this was a quick way using the tools I had at hand – DBCC, perl, elastic search and kibana.

All I did was capture the output of:

DBCC SQLPERF(logspace)

into a temporary table using a simple perl script using DBI. I then did a SELECT against the temporary table in the same perl script and posted the resulting data into ES using the Search::Elasticsearch CPAN module. The ES index was very simple – just 4 columns: database, logspace, logspaceused (%) and the timestamp of the capture (GETDATE()).

After that, all I had to do was visualize the data using Kibana. Here’s some sample output:

 

logspaceused graph
Logspace (%) usage over time

A great way to see the log space pressure points which can easily be tied back to specific batch processes at those times.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.