SQL Function: ListDrives

It seems that I am constantly needing to look at the available drives and free space on each SQL Server for one thing or another. I for one am tired of using the xp_fixeddrives extended sproc and a temp table to get started. On top of that, it doesnt give me everything I realy need to know about each drive. So I wrote a Table function that can be joined into a query an provides almost every bit of information I can get on each drive.

SELECT * FROM [dbaadmin].[dbo].[dbaudf_ListDrives] ()

Returns a Table with the following Columns:

• DriveLetter
• TotalSize Something really needed to accurately determine an accurate percent free .
• AvailableSpace
• FreeSpace
• DriveType
• SerialNumber
• FileSystem
• IsReady This is handy in a clustered environment to see which drives are part of the cluster group.
• ShareName
• VolumeName
• Path
• RootFolder

Some of these values are redundant but I wanted to include every Property of the "Scripting.FileSystemObject". Feel free to provide feedback and let me know if improvements can be made.

here is the code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[dbaudf_ListDrives]()

RETURNS @DriveList Table

(

[DriveLetter] CHAR(1)

,[TotalSize] BigInt

,[AvailableSpace] BigInt

,[FreeSpace] BigInt

,[DriveType] VarChar(50)

,[SerialNumber] VarChar(50)

,[FileSystem] VarChar(50)

,[IsReady] VarChar(50)

,[ShareName] VarChar(255)

,[VolumeName] VarChar(255)

,[Path] VarChar(2048)

,[RootFolder] VarChar(2048)

)

AS

BEGIN

DECLARE @DriveLoop INT

DECLARE @fso Int

DECLARE @DriveCount INT

DECLARE @Drives Int

DECLARE @Drive Int

DECLARE @Property nVarChar(100)

DECLARE @Results VarChar(8000)

DECLARE @Results_int bigint

SET @DriveLoop = 65

exec sp_OACreate 'Scripting.FileSystemObject', @fso OUT

exec sp_OAGetProperty @fso,'Drives', @Drives OUT

exec sp_OAGetProperty @Drives,'Count', @DriveCount OUT

WHILE @DriveLoop <>

BEGIN

SET @Property = 'item("'+CHAR(@DriveLoop)+'")'

--SET @Property = 'Drives.item("A").DriveLetter'

--exec sp_OAGetProperty @fso,@Property, @Results OUT

exec sp_OAGetProperty @Drives,@Property, @Drive OUT

exec sp_OAGetProperty @Drive,'DriveLetter', @Results OUT

IF @Results = CHAR(@DriveLoop)

BEGIN

INSERT INTO @DriveList ([DriveLetter]) VALUES(@Results)

exec sp_OAGetProperty @Drive,'TotalSize' , @Results_int OUT; UPDATE @DriveList SET [TotalSize] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'AvailableSpace' , @Results_int OUT; UPDATE @DriveList SET [AvailableSpace] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'FreeSpace' , @Results_int OUT; UPDATE @DriveList SET [FreeSpace] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'DriveType' , @Results OUT; UPDATE @DriveList SET [DriveType] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'SerialNumber' , @Results OUT; UPDATE @DriveList SET [SerialNumber] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'FileSystem' , @Results OUT; UPDATE @DriveList SET [FileSystem] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'IsReady' , @Results OUT; UPDATE @DriveList SET [IsReady] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'ShareName' , @Results OUT; UPDATE @DriveList SET [ShareName] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'VolumeName' , @Results OUT; UPDATE @DriveList SET [VolumeName] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'Path' , @Results OUT; UPDATE @DriveList SET [Path] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'RootFolder' , @Results OUT; UPDATE @DriveList SET [RootFolder] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

END

SET @DriveLoop = @DriveLoop +1

END

RETURN

END

GO

Not Having Much Luck.

ok, I started with the CD and it died twice right after the repository set-up. It came back with an error reading the CD and the only option I had was to reboot. I then tried it from a 4GB USB Thumb Drive ,which did get farther in to the install but still came back with a media failure. I had even run the media tests in the process and it found nothing wrong with the media. I was trying this with the X86_X64 media and will give it one more try with the i386 media but I am not sure of why the other one wouldn't work.

this is not instilling much faith in Fedora when I know I can take get Ubuntu to install on this box flawlessly every time.

Im downloading the iso now and should have time for one last try tonight.

MOM - Query System Center Reporting Pruning Info

I created this query to identify how much work was needed to prune the database and what space would be gained. This is my first step to an automated pruning job that would always keep the database within the limits of the disk(s) it sits on.

CODE SNIPPIT:

DECLARE @OldestDate DateTime

DECLARE @NewestDate DateTime

DECLARE @DaysStored INT

SELECT @OldestDate = min(LocalDateTimeGenerated)

,@NewestDate = max(LocalDateTimeGenerated)

,@DaysStored = DATEDIFF(day,@OldestDate,@NewestDate)

FROM dbo.SC_EventFact_Table

--SELECT @OldestDate,@NewestDate,@DaysStored

select t1.*

,t2.TableName

,t3.PropertyName AS [ColumnName]

,t5.rowcnt AS [Rows]

,t5.rowcnt/@DaysStored AS [RowsPerDay]

,t5.reservedMB AS [SizeMB]

,t5.reservedMB/@DaysStored AS [SizeMBPerDay]

,@DaysStored-T1.GroomDays AS [DaystoGroom]

,(@DaysStored-T1.GroomDays)

* (t5.reservedMB/@DaysStored) AS [SizeMBtoGroom]

From dbo.SMC_Meta_WarehouseClassSchema t1

join dbo.SMC_Meta_ClassSchemas t2

on t2.classid = t1.classid

left join dbo.SMC_Meta_ClassProperties t3

on t3.classid = t1.classid

left join dbo.SMC_Meta_WarehouseClassProperty t4

on t4.ClassPropertyID = t3.ClassPropertyID

left join (

select object_name(id) TableName

,max(rowcnt) rowcnt

,sum(reserved)*8/1024 reservedMB

From sysindexes

GROUP BY id

) t5

on t5.TableName = T2.TableName

WHERE t4.IsGroomColumn = 1

order by t5.rowcnt desc