travis' brain dump

Ok, I figured it’s time to post something worthwhile to the geek community. 🙂

Have you ever needed to get stats (via SQL) on all your drives and just get the list of your lettered drives and not your mount points? Kind of a pain in the rear, huh? Never fear, here’s a solution for you!

This all came into play while working on getting some daily reports out for SQL servers that expanded beyond what SCOM could accomplish for my needs. I know some Ops Manager folks out there who just love to say ‘yeah, i can do something like that in SCOM’ but let’s face it… your typical DBA hates your monitoring products so here’s a little something for the SQL freaks out there. Sean Boling, I’m looking at you.

So, we’re using a little C# code here, compiled into a DLL and called via a function. I’ll drop some info below on how to use this in conjunction with David Wisemans DBA Checks Script below so you can put it to good use if you so choose.

Overview of the steps:

1. Create a database to run your DBA Utils from… please don’t put this in master.
2. Configure your SQL server to allow CLR (this is the worst part to stomach, I promise)
3. Create your assembly by pulling in your dll. You must compile this yourself as I won’t be handing out dll’s.
4. Create your function.
5. Run some T-SQL against it to test.
6. Incorporate into your monitoring routines.

Section 1. Pre-Req Steps (covers steps 2-4)

(2 – Configure your SQL Server to allow CLR and allow the DB to run your Assembly)

sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO

ALTER DATABASE [DB You Will Create Assembly In] SET TRUSTWORTHY ON;
GO

(3 – Create your DLL & Assembly)

You can download the source code for the DLL from -> HERE <-.

Once compiled, place in a folder you can reach from the machine, in this case C:TEMP.

CREATE ASSEMBLY DiskInfo
FROM ‘C:TEMPDiskInfo.dll’
WITH PERMISSION_SET = UNSAFE
GO

(4 – Create your Function)

/* Object: UserDefinedFunction [dbo].[Get_DiskInfo] */
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[GET_DiskInfo]()
RETURNS TABLE (
Drive_MountPoint nvarchar(max),
Capacity_MB nvarchar(max),
Used_Space_MB nvarchar(max),
Free_Space_MB nvarchar(max),
Percent_Free_Space nvarchar(max)
)
AS
EXTERNAL NAME DiskInfo.UserDefinedFunctions.GET_DiskInfo
GO

 

(5 – Test It all Out)

You can accomplish this one of two ways… If you want to just dump out the info and see what it looks like, use the following:

SELECT  * FROM dbo.Get_DiskInfo()

If you want to get fancy with it and learn more of what you can do with it, try this:

 

DECLARE @MBytesToTB float,@MBytesToGB float
SELECT @MBytesToTB = 1048576,@MBytesToGB = 1024;

SELECT drive_mountpoint,
CASE WHEN capacity_mb > @MBytesToTB THEN CAST(ROUND(capacity_mb /@MBytesToTB,1) as varchar) + ‘ TB’
WHEN capacity_mb > @MBytesToGB THEN CAST(ROUND(capacity_mb /@MBytesToGB,1) as varchar) + ‘ GB’
ELSE capacity_mb + ‘ MB’ END as Size,

CASE WHEN used_space_mb > @MBytesToTB THEN CAST(ROUND(used_space_mb /@MBytesToTB,1) as varchar) + ‘ TB’
WHEN used_space_mb > @MBytesToGB THEN CAST(ROUND(used_space_mb /@MBytesToGB,1) as varchar) + ‘ GB’
ELSE used_space_mb + ‘ MB’ END as Used,

CASE WHEN free_space_mb > @MBytesToTB THEN CAST(ROUND(free_space_mb /@MBytesToTB,1) as varchar) + ‘ TB’
WHEN free_space_mb > @MBytesToGB THEN CAST(ROUND(free_space_mb /@MBytesToGB,1) as varchar) + ‘ GB’
ELSE free_space_mb + ‘ MB’ END as Free,
ROUND(Percent_Free_Space,2) AS PercentFree
FROM dbo.GET_DiskInfo()
ORDER BY PercentFree ASC

 

For those of you who are using the DBA Daily Checks script by David Wiseman, download a replacement for the stored procedure DBAChecks_DiskDrives -> HERE <-. When using this, you do not need the SQLIO package he created to gather disk information.

As always, use at your own risk and have fun. 🙂