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.
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. 😀
PS: Like Jeff, I'd also be interested in seeing how you can achieve this using standard T-SQL.