T-SQL Tuesday #25 – The Best Tool You’ve Never Used

Wow, it’s been a long time since my last T-SQL Tuesday post and I have disappointingly missed it 5 times for one reason or another. Still, the important thing is that I am back people!

This month’s T-SQL Tuesday is brought to you by Allen White (blog|twitter) who many of you will know for his PowerShell, SMO and SQL Server expertise.

Ironically for this post (or the subject at least) I was saving up towards a larger Data Visualisation article that I was putting together, but when I read the subject of this months T-SQL Tuesday I just knew it was time to spill the beans.

Let me give you the backstory…

Around four years ago, week in and week out I used to religiously buy a Technology Publication just on the “off chance” that they would have something of use in their pages. Very occasionally a little gem would crop up and eventually one day I found what I was looking for. This particular little gem used a rather ingenious way to visualise folder and file space usage and most importantly it was free and fast.

One of the biggest problems I used to experience as a production DBA was the filling of various disk drives. From backups, log, data, tempdb and even system drive we would get it all and having a substantial amount of servers made it a common problem.

When these issues would occur I was very happy to receive them because I knew I could identify the problem area in minutes (if not seconds) and it was always very satisfying to whip out my secret tool (ooo errr missus!)

So why is it so good…

There are of course lots of other space analysis tools on the market, many commercial and many shareware or even free. But most of those I have seen have usually been slow, used poor visualisation and mostly required a local or remote installation. My tool is a simple Windows based executable that does not require installation and can live on your network home folder. This can be mapped across to any server you Remote Desktop into, so hey presto it is always available to you 🙂

One of the biggest usability points of the tool is the ability to zoom into out out of folders very easily and quickly. It is this ability which makes finding problem files and folders incredibly simple.

Imagine the following scenarios :-

  1. Your system drive is close to filling up, you are not sure why and your Windows Administrator is struggling to locate the problem folder.
  2. You have thousands of databases across many instances on a server and your: –
    1. Data drive is full
    2. Log drive is full
  3. Your Backup drive is getting very full. Backup and archive routines appear to be working.

… and some real-world explanations for those situations that I have uncovered in seconds using this awesome utility :-

  1. A DBA left a long running Profiler trace locally on the SQL Server. It transpired that even though the trace file can be saved on any drive, Profiler unfortunately generates a ever growing temporary trace file locally on the C: Drive until it is stopped (a recipe for disaster!).
    1. A mis-behaving bulk loading process caused a database data file to grow out of control.
    2. A long running transaction prevented the log file VLFs from being truncated and caused it to grow out of control.
  2. DBAs did not follow standards and practices and stored VLDB adhoc backups in random locations.

Remember fixing a SQL Server problem is usually pretty easy in most cases, the hard part is identifying where the actual problem lies. With the best Tool you have have never used, even identifying your space issues becomes a breeze.

Ladies and Gentlemen I give you SpaceMonger! Ensure that you download the old v1.4.0 version located on the Free Software tab since this is the superior release. Now imagine using this on a multi TB disk and being able to zoom into your problem. You will be astounded how fast and easy it is.

The more data you have the easier those problem files are to find!

4 thoughts on “T-SQL Tuesday #25 – The Best Tool You’ve Never Used

  1. Pingback: Allen White : T-SQL Tuesday #25 Followup - Just in Time for the Holidays

    1. retracement Post author

      Hi Steven, thanks for that suggestion. I remembered another tool being suggested by someone around 6 months ago and I couldn’t remember what it was called but I wasn’t overly impressed and I’m pretty sure this was the one. Obviously these things are usually down to personal preference so let me say why I’m not a big fan of it.
      The first most obvious niggle I have is that it requires installation. This is probably not such a big deal for local drive analysis, but doing so over a UNC path will be slow(er) and possibly cause unnecessary network IO. Compare that against my version of SpaceMonger and you will see that the exe can be dropped anywhere (I suggest a mapped drive from RDP) which allows you to actually jump on the server having problems to analyse in seconds.
      The speed of analysis is SLOW. On a 180GB SSD, WinDirStat took around 30 seconds to query. SpaceMonger takes several seconds. One of the reasons for this is that my tool is simply visualising space usage rather than trying to be everything to everyone. WinDirStat I can see as being a useful space reporting tool (for instance analysing file type usage) -SpaceMonger is more of a space troubleshooting tool.
      Visually SpaceMonger is streets ahead in my opinion. Its a simple fast interface that doesnt need to get bogged down with fancy graphics and treeview displays. Leading on from the, WinDirStat cannot visualize more than the top level of the dir path. This is useless to me when I want to drill down and this is where SpaceMonger’s visualization element really comes in to play. Image 1000 fairly large transaction log backups for a database but comparatively small when compared to other files on the drive. I could very easily drill into the backups area of a drive and as I change context and zoom in, the sizes of files becomes relative to where you are currently at.
      WinDirStat definately has some use cases (and I cannot argue against that) but for me, SpaceMonger 1.4 hasn’t been beaten by anything else I’ve seen on the market to date.

Comments are closed.