SQL Server clustered instance dependencies – verification – using tsql, xp_cmdshell

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using tsql, xp_cmdshell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

sqldiskdep

The code can be downloaded from here

Display folder size in a graphical way using SQL Server

Goal

To obtain the size of folders and their path in graphical way. To have the output of tree command, dir command and the folder size in only one output.

My way of achieving it

Using tsql, xp_cmdshell in order to call the tree, dir and powershell.exe command s.  The desire to have something like that came when I have to troubleshoot and see who is the biggest consumer in case of free space issues. Of course that multiple approaches and solutions can solve this question but I stopped on using the tree command because I liked the look of the output. The first output shows the information as the tree command does while the other tries to sort them by size in order to easy see the biggest consumer. One problem with this is that for locations where sql server doesn’t have access the size returned is 0. Even so I decided to finish the script and use it like this because of the output. The script has also a deep switch that will let you decide how deep you want to traverse the folders, maximum is 3.

The output of the script looks like this

sqlservertreelikeoutput

The code can be downloaded from here