RG062 – The WM Log Management Tool

By Max Ranzau

 

From the Community Hero Dept. It is my pleasure to present to you a cool addition to the community tool set, surrounding RES Software products. I want to highlight this one in particular as it addresses a situation which has needed attention for a while. (read: I’ve been moaning about it for years  :) It’s about the logs accessible inside the RES Workspace Manager console. Back in 2009 I addressed how to clear individual logs, by doctoring the RES WM datastore directly – not something for the faint of heart or SQL inexperienced, nor something that is recommended. Nevertheless it’s necessary as even 6 years down the road today, the individual logs still can’t be cleared from the Workspace Manager console. One can only wonder…

In case you’re wondering too; why all the fuss? Let me draw you a picture from an administrative perspective: The ability to do this is crucial as one needs to repeatedly clear the logs to be able to identify and address potential authorizations. I have described the workflow for this in the article How to roll out security in a production environment.

patrickThe WM Log Management Tool (formerly known as SQL Database Logging Cleanup tool, or the DB Cleanup Tool) was developed by my good former RES colleague Patrick van Grinsven. As the tool name indicates, it serves the purpose of cleaning up the RES Workspace Manager logs, however it does this in an intelligent way, as it will only present you with a limited 1.000 record preview in the tool interface, second it will clear logs out in batches of 20.000 records, to prevent overloading the DBMS unnecessarily.

The new version 2.1, released May 2015 has been spiffied up with some cool new graphs and features. Here is an overview of the changes:

  • Disclaimer is shown during application launch (every time, really?)
  • Logging is directly selectable from the list with available logging.
  • Query and cleanup size is adjustable through the Settings | Advanced Settings menu
  • Custom queries are created to represent Workspace Manager logging as close as possible.
  • Query output can be saved to .csv format (up to 30k records for each query)
  • Pie chart and Trend chart added for analysis purposes (default overview of 3 months for Trend and Total overview for the Pie chart).
  • Logs can be analyzed separately and cumulative.

logclean1

logclean2

 

While the interface is designed to be as self-explanatory as possible, here is a brief description of how to work the tool:

  1. Enter your SQL server credentials – Windows authentication is also possible, the checkmarks appear when a value has been entered into the required field. NOTE: Only SQL Server 2005 and up are supported at this moment, so the tool cannot be used for SQL express, Oracle, DB2 or other databases.
  2. When all fields are validated the Analyze button will become available and an analysis of the logging database can be started.
  3. To see the result of the analysis select a log which you would like to query (output is 1000 records max), or to clear.
  4. Before clearing begins, the number of records of the selected log will be determined and cleared. When clearing has finished, the logging table will be analyzed again: A query on the selected log will be done, to see if records were added after the clearing.
  5. The SQL database will need to be shrunk after the operation, which will physically purge the unwanted records. There is a technet article here on how to do this. Patrick tells me that this is a feature he may be implementing in the future.

The tool requires .NET Framework 4.5 or newer to be installed. The version 2.0 of the tool was tested with 300k+ entries in the errors log and it took approx. 1½ min to clear. For performance purposes, it’s best to run the tool on a computer located on the same network as where the SQL server is situated.

And before I let you on to the goodies, let’s just agree to the following: The tool is provided to the community as-is. There is no warranty nor is it supported by RESguru.com. For any legal dispute, both Patrick and I will kindly refer you to the M.O.A.D.

Click here to download new DBCleanupTool 2.1: Download-icon-cropped

 

 

2 Comments

  • By Nate Ravid, December 15, 2015 @ 06:17

    Is it possible that this utility does not work with SQL 2012? I am receiving Login Failure for the datastore when connecting.

  • By Patrick van Grinsven, December 16, 2015 @ 00:35

    Nate,

    It should work but make sure that you are using a sql account and not a domain account.
    In the upcoming version it will also be possible to use Windows Authentication to connect to the database.

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment

Comments are welcome as always. Just do the math below. * Time limit is exhausted. Please reload the CAPTCHA.