Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


fn_fixeddrives() : an alternative for xp_fixeddrives


fn_fixeddrives() : an alternative for xp_fixeddrives

Author
Message
jr81
jr81
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 602
Hi, Will this report disk/drive space for mounted Volumes?
Thanks
Harold Buckner
Harold Buckner
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 420
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
David BAFFALEUF
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 712
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
Matt Whitfield
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 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! :-D

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
David BAFFALEUF
David BAFFALEUF
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 712
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23515 Visits: 18271
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLior
SQLior
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54854 Visits: 40396
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David BAFFALEUF
David BAFFALEUF
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 712
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. :-D

Thanks again,

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

David B.
Matt Whitfield
Matt Whitfield
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search