Tuesday, April 23, 2013

WinDirStat for SQL Server DBAs

WinDirStat can be used to quickly assess the drive usage by focusing on the relative usage of the drive.   As a SQL Server DBA, disk volume and usage may not be your direct purview, but undoubtedly it can affect you.  Usually it will affect you at the worst time: The disk is full and SQL Server database and supporting files are to blame.  The immediate task affecting the DBA will be to get the disk usage under control - this requires you to assess how to quickly recover disk space in low risk areas.   A follow-on task to such an issue will be to then assess capacity and make recommendations.  WinDirStat is a free, open source tool that can help in these situations.

The three pane layout of WinDirStat allows you to simultaneously see and compare the following aspects: 
  • Relative Size per folder
  • Relative Size per file type
  • Proportional graphical view of each file

For example, in the below screen shot, a drive was nearly 99% full.  This is a simple example because the drive is organized very well already and has a very specific use.  It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue.  And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.

Example #1:

This is a simple example because the drive is organized very well already and has a very specific use.  It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue.  And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.

In this example, a more multipurpose volume on a development server, seeing the three aspects (by folder, by file type, and the graphical view) showed a more nuanced picture. 

Example #2:

The largest folder is the 'BackupsMonthly'.  The largest file type was 'BAK File' - SQL Server Backups.  Clicking the large blue squares revealed the largest SQL Server Backups.  But in this case, those files were relatively stable in size, that is, a certain amount of backup files were necessary. 

Secondarily, this revealed a large usage of the Recycle Bin (@RECYCLE.BIN) which was easily cleaned.  Also a few large disk images ('ISO' shown as associated with 'Virtual CloneDrive' in the image above) related to software that was a candidate to be relocated to another server.

How to get WinDirStat
WinDirStat is a free, open source tool.  It can be downloaded from WinDirStat.info.  Since it is open source, the program can be modified or extended and through the folks at PortableApps.com, there is a portable version available.

I recommend acquiring the portable version of WinDirStat.  Portable WinDirStat requires no installation, so the files can simply be copied - on your workstation, a network shared folder, or onto your SQL Servers themselves