fn_fixeddrives() : an alternative for xp_fixeddrives

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Comments posted to this topic are about the item fn_fixeddrives() : an alternative for xp_fixeddrives

    David B.

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    While I like the idea, I'm not sure about storing the drive information in a static field - you would be much better off declaring a class or struct in which to return results, using a generic List<> to store your instances, and return the data from there.

    That way, you don't need the UNSAFE permission set, and achieve exactly the same thing.

    Edit -> Ok, you still need UNSAFE because of the permissions required by the DriveInfo class, but I still wouldn't use a static 😀

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • blessonblessons

    SSC Eights!

    Points: 949

    Nice idea.thanks for sharing

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Nice stuff. Did I miss the link where I can download the source code and/or compiled object??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jack Corbett

    SSC Guru

    Points: 184381

    I thought this was a well-written and clear article which helped me better understand working with the CLR. I still haven't found a need to do any CLR programming and I'm not really looking for any, but it is good to understand.

    Matt,

    Why would using a class or struct make it unnecessary to use UNSAFE? I had just assumed that going out to the OS to get the drive information would necessitate UNSAFE. I'm a total newbie with CLR.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Sorry Kevin,

    Here you go: https://sourceforge.net/projects/fnfixeddrives/[/url]

    David B.

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    Jack

    For going out to the OS, you generally can use EXTERNAL_ACCESS. In this instance, you actually do need UNSAFE - because of the permissions that are required to use DriveInfo under CAS. I usually check these things before I open my big mouth! 🙂

    But I still wouldn't use a static field even so, seeing as the static field's life time will extend beyond the execution of the function (for example, if you use a singleton under UNSAFE, you can observe that it's lifetime is longer than that of the call into the CLR - subsequent calls to the same function would use the same instance).

    Anyway, the code I would use would look like this:

    sealed class _driveInfo

    {

    public readonly string DriveName;

    public readonly long TotalSizeMB;

    public readonly long UsedSizeMB;

    public readonly long FreeSizeMB;

    public readonly long PercentUsed;

    public _driveInfo(string driveName, long totalSizeMB, long usedSizeMB, long freeSizeMB, long percentUsed)

    {

    DriveName = driveName;

    TotalSizeMB = totalSizeMB;

    UsedSizeMB = usedSizeMB;

    FreeSizeMB = freeSizeMB;

    PercentUsed = percentUsed;

    }

    }

    [Microsoft.SqlServer.Server.SqlFunction

    (

    FillRowMethodName = "_f_fill",

    TableDefinition = "Drive nvarchar(3), SizeMb bigint, UsedMb bigint, AvailableMb bigint, UsePct bigint"

    )

    ]

    public static IEnumerable fn_fixeddrives()

    {

    foreach (DriveInfo d in DriveInfo.GetDrives())

    {

    if (d.DriveType == DriveType.Fixed && d.IsReady)

    {

    yield return new _driveInfo(d.Name,

    (d.TotalSize) / 1048576,

    (d.TotalSize - d.TotalFreeSpace) / 1048576,

    (d.TotalFreeSpace) / 1048576,

    (d.TotalFreeSpace * 100) / d.TotalSize);

    }

    }

    }

    private static void _f_fill(Object o, out string drvName, out long totalsizeMb, out long usedsizeMb, out long freesizeMb, out long pctused)

    {

    _driveInfo dI = (_driveInfo)o;

    drvName = dI.DriveName;

    totalsizeMb = dI.TotalSizeMB;

    usedsizeMb = dI.UsedSizeMB;

    freesizeMb = dI.FreeSizeMB;

    pctused = dI.PercentUsed;

    }

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Hi Matt,

    I agree using a static field will force the assembly to be considered as UNSAFE, but diving into the IO system would lead to the same path anyway.

    I use a static field because I need the list to be available outside of the main scope, especially in the fill method. What you will usually see in sqlclr developments is to pass the full object to the fill method, because it is more convenient, but you'll have to call a DriveInfo constructor to get the job done. The idea in this example was to pass a simple array pointing to fixed and ready drives, so you save a call to the object's ctor. And remember a call to the object's ctor will be called every time a row is fetched from the list.

    Appreciate for the comments ,

    David B.

  • fhanlon

    SSChampion

    Points: 13131

    Nicely written. Thanks for the explanation



    Francis

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    David

    Yes, the DriveInfo class does require UNSAFE because of the permissions, I said that - my bad.

    However, I can't agree with the rest of what you've said. I don't see that the constructor of a simple class would outweigh the cost of using an ArrayList instead of yield as well as the cost of boxing and unboxing all of the values stored in that ArrayList...

    The other worry would be garbage collection. By putting everything in a static, the DriveInfo instances that are created are not naturally garbage collected because the reference to them is held by the static array. By doing away with that, everything is up for garbage collection as soon as the function terminates...

    M

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • jr81

    SSC-Addicted

    Points: 448

    Hi, Will this report disk/drive space for mounted Volumes?

    Thanks

  • Harold Buckner

    SSCarpal Tunnel

    Points: 4008

    Hey david

    Will this also gather mount points? We use a lot of mountpoints here to save on drive letters.

    Opps, we must have posted at the same time. LOL

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Matt Whitfield (9/21/2010)


    David

    Yes, the DriveInfo class does require UNSAFE because of the permissions, I said that - my bad.

    However, I can't agree with the rest of what you've said. I don't see that the constructor of a simple class would outweigh the cost of using an ArrayList instead of yield as well as the cost of boxing and unboxing all of the values stored in that ArrayList...

    The other worry would be garbage collection. By putting everything in a static, the DriveInfo instances that are created are not naturally garbage collected because the reference to them is held by the static array. By doing away with that, everything is up for garbage collection as soon as the function terminates...

    M

    Hi Matt,

    You made your point, especially on the GC part, which I hadn't considered in the design. That's what I like the most about comments, the idea of debate. And there's always something good to learn in the end.

    Thanks again for your comments,

    David B.

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    David BAFFALEUF (9/21/2010)


    And there's always something good to learn in the end.

    If I had a pound for every time I'd learnt something either here or on Ask.SSC then I'd be a lot richer! 😀

    And please don't get me wrong, I think the article is great - the more people that know about how to use the CLR the better...

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Hi Harold / Jayant,

    Very good question. What is clear is that DriveInfo does not consider mount points, so they won't be reported by the sqlclr function. There are ways to deal with volumes and mount points like using the system service API directly (using a DllImport on kernel32.dll in the source code). You could do it in a separate piece of managed C# console app, but I've not tested it in SQLCLR. You should be very carefully testing this because there are much more potential causes of unhandled exceptions this way.

    Thanks for your comments,

    David B.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply