Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

fn_fixeddrives() : an alternative for xp_fixeddrives Expand / Collapse
Author
Message
Posted Tuesday, September 21, 2010 7:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:11 PM
Points: 24, Visits: 559
Hi, Will this report disk/drive space for mounted Volumes?
Thanks
Post #990305
Posted Tuesday, September 21, 2010 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 9:59 AM
Points: 307, Visits: 385
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
Post #990309
Posted Tuesday, September 21, 2010 8:17 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
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.
Post #990337
Posted Tuesday, September 21, 2010 8:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
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
Why I wrote a sql query analyzer clone
Post #990342
Posted Tuesday, September 21, 2010 9:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
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.
Post #990418
Posted Tuesday, September 21, 2010 10:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:11 PM
Points: 17,601, Visits: 15,465
Thanks - this was a good article. It is nice to see stuff like this in CLR.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #990507
Posted Tuesday, September 21, 2010 2:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 25, 2013 2:12 PM
Points: 341, Visits: 55
Hi David,
I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article.

Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.

Your solution is not so practical because -
* I don't have access to the server's local drive - I need it to be able to copy the DLL.
* I am not allowed to even turn on the CLR option.
* The function is unsafe and in corporate environments I just don't have the permissions to install it.
* DBAs frown upon CLR.

I can come up with more reasons, but you get the idea.

Now, it so happens that I needed the same feature just last week.
I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.
I named it sp_fixeddrives and put it in master. This enables me to call it from any db.
I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.

Post #990766
Posted Tuesday, September 21, 2010 6:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
SQueaLer1 (9/21/2010)
Hi David,
I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article.

Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.

Your solution is not so practical because -
* I don't have access to the server's local drive - I need it to be able to copy the DLL.
* I am not allowed to even turn on the CLR option.
* The function is unsafe and in corporate environments I just don't have the permissions to install it.
* DBAs frown upon CLR.

I can come up with more reasons, but you get the idea.

Now, it so happens that I needed the same feature just last week.
I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.
I named it sp_fixeddrives and put it in master. This enables me to call it from any db.
I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.



Any chance of you posting your tweaked code for this? I'd love to see the T-SQL alternative to such as this. Thanks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #990831
Posted Wednesday, September 22, 2010 1:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
SQueaLer1 (9/21/2010)
Hi David,
I appreciate the concept but I have to disagree with the approach. We programmers can come up with a lot of "nice" solutions, but they don't always work in the corporate world. This is a factor you haven't touched on in your article.

Remember, we have total control over our PC with our local SQL, but in large enterprise environments, the situation is completely different.

Your solution is not so practical because -
* I don't have access to the server's local drive - I need it to be able to copy the DLL.
* I am not allowed to even turn on the CLR option.
* The function is unsafe and in corporate environments I just don't have the permissions to install it.
* DBAs frown upon CLR.

I can come up with more reasons, but you get the idea.

Now, it so happens that I needed the same feature just last week.
I implemented it in PURE SQL using just one short stored procedure. Similar code has been out there for some time. I tweaked it for my needs. I think it is generic enough to be used by anyone.
I named it sp_fixeddrives and put it in master. This enables me to call it from any db.
I also created a job that executes it hourly, and checks the free space %. If it falls below a threshold, I send out an alert email.



Hi,

Thank you, this was the comment I was hoping to read

I understand your position, but because I use this in an operationnal monitoring tool for a while on many business critical environments, I cannot agree on all points. It really depends on your role in the database infrastructure. I am considered as the DBA on the systems I monitor for my clients, so I am pretty much on my own to apply the method I want, but this is not the point.

The problem is because SQLCLR is not so well understood by the DBAs in general. And for a DBA, who carries the responsability of the corporation's data management and safety, what is not so well understood will wait on the doorway until it is, if not thrown away. I've been a production DBA for the past 10 years, I know how conservative we can be with new *cool* features.

The message I tried to send under the covers was that SQLCLR exists, and to use it you must understand what it can do and what it can't. There is a hazard that comes with practically every tool but it is not a valid reason for me not to learn from it. Same thing applies to COM automation in SQL Server, which is fantastic tool, but because the warnings have always been a square mile big, very few people uses it.

Because of all these assumptions, SQLCLR is still in its infancy, like LINQ for example which already turned DBAs against it even before it had touched the market floor. This is why sqlclr.net exists and we try to show the guys in control SQLCLR is not that evil. I know this is a long way to convince DBAs it is better to build bridges across production IT and developpers, but we'll hold.

Thanks again,

PS: Like Jeff, I'd also be interested in seeing how you can achieve this using standard T-SQL.


David B.
Post #990902
Posted Wednesday, September 22, 2010 2:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
David BAFFALEUF (9/22/2010)
The problem is because SQLCLR is not so well understood by the DBAs in general. And for a DBA, who carries the responsability of the corporation's data management and safety, what is not so well understood will wait on the doorway until it is, if not thrown away. I've been a production DBA for the past 10 years, I know how conservative we can be with new *cool* features.

Truer words rarely spoken. I think the SQL Server community as a whole is very slow to adopt new technology - which can be frustrating. However, it's probably that same characteristic that has made it one of the longest serving job skills in the IT industry as a whole...


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #990926
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse