SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CLR mountpoint freespace query scaling *really bad* with high number of mountpoints


CLR mountpoint freespace query scaling *really bad* with high number of mountpoints

Author
Message
Theo Ekelmans
Theo Ekelmans
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3630 Visits: 880
*EDIT* Workaround available at the end of this discussion chain



Hi All,

In order to get to the size and free space info on mountpoints i used several variations on this theme:

EXEC master..xp_cmdshell 'wmic logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'

and then decode what came back, not pretty, but hey... it worked and was reasonably fast, about 100-400ms or so.

But due to some weird DR STRETCHED SAN config fubar of our hosting supplier we found ourselves unable to expand mountpoints on the DR stretched 3 way cluster, and each time when we ran out of space we simply piled on more and more mountpoints and files to databases.

Don't shoot the messenger guys.... i just inherited this setup and have to deal with it until it reaches it's intended lifespan.

As the mountpoint number went up, the runtime of the xp_cmdshell / wmic took longer and longer to complete, up to several minutes, and i started to worry Sad

So convinced something had to be done, i grabbed a C# book and did some serious catching up on my aging 30 year old C knowledge (yeah, i still have the white/blue kernighan and richie book from my school days), and wrote my first CLR.

After the struggling with hello world issues that come with using Visual Studio 2013 C# for the first time, i got a cmd window working with precisely what i needed. But only to find out that .NET SQL CLR is "some what limited" in the things you can use.

Long story short, (and with many a thanks to all who share code snippets) this is what i came up with; a CLR that does 3 things:
- Physical disk info (no mountpoint support)
- Logical disk info (with mountpoint support)
- and a SPLIT function.

Here's my code (https://www.dropbox.com/s/jt8zvgk47ptql3q/CLR.sql?dl=0)

----------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections; /// IEnumberable
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.IO; /// DriveInfo
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{

//-------------------------------------------------------------------------------------
// Logical volume info (works for mountpoints)
//-------------------------------------------------------------------------------------
/*
AvailableFreeSpace Indicates the amount of available free space on a drive.
DriveFormat Gets the name of the file system, such as NTFS or FAT32.
DriveType Gets the drive type.
IsReady Gets a value indicating whether a drive is ready.
Name Gets the name of a drive.
RootDirectory Gets the root directory of a drive.
TotalFreeSpace Gets the total amount of free space available on a drive.
TotalSize Gets the total size of storage space on a drive.
VolumeLabel Gets or sets the volume label of a drive.
*/

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spClrLogicalDriveInfo()
{
string serverName = Environment.MachineName;

PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);

SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("DriveLetter", SqlDbType.NVarChar, 256),
new SqlMetaData("TotalSize", SqlDbType.BigInt),
new SqlMetaData("TotalFreeSpace", SqlDbType.BigInt),
new SqlMetaData("DriveFormat", SqlDbType.VarChar, 32),
new SqlMetaData("DriveType", SqlDbType.VarChar, 32),
new SqlMetaData("VolumeLabel", SqlDbType.VarChar, 20),
new SqlMetaData("AvailableFreeSpace", SqlDbType.BigInt));

SqlContext.Pipe.SendResultsStart(record);

foreach (string instanceName in pcc.GetInstanceNames())
{
PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);

float percentfree = pcPercentFree.NextValue();

if (percentfree == 0) { percentfree = 1; };

float TotalFreeSpace = pcFreeMbytes.NextValue();
float TotalSize = (TotalFreeSpace * 100) / percentfree;

if (instanceName != "_Total")
{
record.SetSqlString(0, instanceName + @"\"); //DriveLetter
record.SetSqlInt64 (1, Convert.ToInt64(TotalSize)); //TotalSize
record.SetSqlInt64 (2, Convert.ToInt64(TotalFreeSpace)); //TotalFreeSpace
record.SetSqlString(3, ""); //DriveFormat (not supported by PerfMon)
record.SetSqlString(4, ""); //DriveType (not supported by PerfMon)
record.SetSqlString(5, ""); //VolumeLabel (not supported by PerfMon)
record.SetSqlInt64 (6, Convert.ToInt64(0)); //AvailableFreeSpace (not supported by PerfMon)
SqlContext.Pipe.SendResultsRow(record);
}
}
SqlContext.Pipe.SendResultsEnd();
}

//-------------------------------------------------------------------------------------
// Performance counters
//-------------------------------------------------------------------------------------

// ToDo
};

public partial class UserDefinedFunctions
{

//-------------------------------------------------------------------------------------
// Physical disk info (no mountpoint support)
//-------------------------------------------------------------------------------------
/*
AvailableFreeSpace Indicates the amount of available free space on a drive.
DriveFormat Gets the name of the file system, such as NTFS or FAT32.
DriveType Gets the drive type.
IsReady Gets a value indicating whether a drive is ready.
Name Gets the name of a drive.
RootDirectory Gets the root directory of a drive.
TotalFreeSpace Gets the total amount of free space available on a drive.
TotalSize Gets the total size of storage space on a drive.
VolumeLabel Gets or sets the volume label of a drive.
*/

[SqlFunction(FillRowMethodName = "FillRow",
TableDefinition = "DriveLetter nvarchar(256)," +
"TotalSize bigint null," +
"TotalFreeSpace bigint null," +
"DriveFormat nvarchar(32) null," +
"DriveType nvarchar(32) null," +
"VolumeLabel nvarchar(20) null," +
"AvailableFreeSpace bigint null"
)]

public static IEnumerable fnClrDriveInfo()
{
return System.IO.DriveInfo.GetDrives();
}

public static void FillRow(Object obj
, out string DriveLetter
, out SqlInt64 TotalSize
, out SqlInt64 TotalFreeSpace
, out string DriveFormat
, out string DriveType
, out string VolumeLabel
, out SqlInt64 AvailableFreeSpace
)
{
DriveInfo drive = (DriveInfo)obj;
DriveLetter = drive.Name;
DriveType = drive.DriveType.ToString();

// Check if the drive is ready (cdrom drives, SD cardreaders etc)
if (drive.IsReady)
{
TotalSize = new SqlInt64(drive.TotalSize) / 1048576;
TotalFreeSpace = new SqlInt64(drive.TotalFreeSpace) / 1048576;
DriveFormat = drive.DriveFormat;
VolumeLabel = drive.VolumeLabel;
AvailableFreeSpace = drive.AvailableFreeSpace / 1048576;
}
else
{
TotalSize = new SqlInt64();
TotalFreeSpace = new SqlInt64();
DriveFormat = null;
VolumeLabel = null;
AvailableFreeSpace = new SqlInt64();
}
}

//-------------------------------------------------------------------------------------
// Split replacement
//-------------------------------------------------------------------------------------

[SqlFunction(Name = "fnClrSplit",
FillRowMethodName = "FillSplitRow",
TableDefinition = "txt nvarchar(10)")]

public static IEnumerable fnClrSplit(SqlString str, SqlChars delimiter)
{
if (delimiter.Length == 0)
return new string[1] { str.Value };

return str.Value.Split(delimiter[0]);
}

public static void FillSplitRow(object row, out SqlString str)
{
str = new SqlString((string)row);
}

};


----------------------------------------------------------------------------------------------------------------------


And after some testing i rolled it out, hoping my mountpoint info would be returned much faster...

And murphy made damn sure it was'nt.... not even by a long shot :'(

Having learned the hard way never to trust just one measurement, I tested it against 45 SQL instances, and then something caught my eye.....



The more mountpoints there are, the longer it took, and the curve was *no way near linear*.

So this is where i am now.... ,having almost run out of available letters of the alphabet, having no way to stop the grow of the mountpoints or databases, and fast loosing the ability to check to see if the mountpoints are running out of space from within SQL.

So i'm hoping some fellow DBA out there, cursed by as many a mountpoint as i am had cracked a way to get stable performing free space info on mountpoints?

Suggestions anyone?
Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12065 Visits: 3290
Theo Ekelmans (2/24/2015)
After the struggling with hello world issues that come with using Visual Studio 2013 C# for the first time, i got a cmd window working with precisely what i needed. But only to find out that .NET SQL CLR is "some what limited" in the things you can use.


Hi Theo. So, by the statement above, are you meaning that the console app runs the code quickly and that it is only slow when executed within SQL Server via SQLCLR?

As far as I can see, your code for the two drive space methods looks fine.

For the Split function there are two improvements that should be made to make it much faster:
1) in the SqlFunction attribute, add the property: IsDeterministic=true (as in "SqlFunction(IsDeterministic = true)")

2) You don't need SqlChars for the delimiter. You could use SqlString. But if you are going to use SqlChars:

a) you might be better off referencing it via "Delimiter.Buffer[0]"

b) If you are publishing via Visual Studio / SSDT, be sure to add a SqlFacet to the delimiter parameter to prevent it from automapping SqlChars to NVARCHAR(MAX) (since NVARCHAR(MAX) will slow down the operation so only use it when necessary): "[SqlFacet(MaxSize = 10)] SqlChars delimiter"

Also, while not making it faster, you should map the output field of "txt" to NVARCHAR(4000) instead of NVARCHAR(10). There is no reason to not do this and it is more flexible as it can handle any string passed in, including a 4000 characters string with no delimiter.



You can get some SQLCLR functionality (including the Split and GetDriveInfo functions) already done and for free via the SQL# library (which I am the author of).

And for more info on working with SQLCLR in general, I am writing a Stairway series here on SQL Server Central: Stairway to SQLCLR

Take care,
Solomon...

SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
Theo Ekelmans
Theo Ekelmans
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3630 Visits: 880
Hi Solomon,

Thanks for the all tips, and i will be sure to use them.

By "But only to find out that .NET SQL CLR is "some what limited" in the things you can use." i ment that i got errors when i tried to use this construct in the CLR

ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");
ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
ManagementObjectCollection queryCollection = searcher.Get();

It seems that ManagementObjectSearcher is .NET 2 based an not allowed in a CLR.

It would have solved my performance problem, because if i use it in my console app, it rips through all 139 mountpoints in 500 ms flat; hundreds of times faster then using the perfmon counter workaround.

console app source
-------------------------------------------------------------------------------------

//-------------------------------
// Win32_Volume version
//-------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Management;
using System.IO;

namespace ConsoleApplication1
{
class Program
{
static void Main()
{

DateTime StartDT = DateTime.Now;

DateTime StartStepDT;
DateTime EndStepDT;
double StepRuntimeMs;
double EnumRuntimeMs;

StartStepDT = DateTime.Now;


ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");
ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
ManagementObjectCollection queryCollection = searcher.Get();

EndStepDT = DateTime.Now;

StepRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;
Console.WriteLine("Init Runtime: {0, 8} ms", StepRuntimeMs);


string sVolumeLabel = "";
//string[,] saReturn = new string[queryCollection.Count, 7];
int i = 0; // counter for foreach

StartStepDT = DateTime.Now;

foreach (ManagementObject m in queryCollection)
{
EndStepDT = DateTime.Now;
EnumRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;

StartStepDT = DateTime.Now;

//if (string.IsNullOrEmpty(Convert.ToString(m["VolumeName"]))) { sVolumeLabel = "Local Disk"; } else { sVolumeLabel = Convert.ToString(m["VolumeName"]); } // Disk Label
//string sSystemName = Convert.ToString(m["SystemName"]); // Name of computer
string sDriveLetter = Convert.ToString(m["Name"]); // Drive Letter

if (m["DeviceID"] != null)
{
decimal dSize = Math.Round((Convert.ToDecimal(m["Capacity"]) / 1048576), 2); //HDD Size in MB
decimal dFree = Math.Round((Convert.ToDecimal(m["FreeSpace"]) / 1048576), 2); // Free Space in MB
decimal dUsed = dSize - dFree; // Used HDD Space in MB

//int iPercent = Convert.ToInt32((dFree / dSize) * 100); // Percentage of free space

//saReturn[i, 0] = sSystemName;
//saReturn[i, 1] = sDriveLetter;
//saReturn[i, 2] = sVolumeLabel;
//saReturn[i, 3] = Convert.ToString(dSize);
//saReturn[i, 4] = Convert.ToString(dUsed);
//saReturn[i, 5] = Convert.ToString(dFree);
//saReturn[i, 6] = Convert.ToString(iPercent);

Console.WriteLine("-----------------------------");
//Console.WriteLine("SystemName : " + sSystemName);
Console.WriteLine("DriveLetter : " + sDriveLetter);
Console.WriteLine("VolumeLabel : " + sVolumeLabel);
Console.WriteLine("Size : " + Convert.ToString(dSize));
Console.WriteLine("Used : " + Convert.ToString(dUsed));
Console.WriteLine("Free : " + Convert.ToString(dFree));


i++; // increase counter. This will add the above details for the next drive.
}
else
{
Console.WriteLine("-----------------------------");
//Console.WriteLine("SystemName : " + sSystemName);
Console.WriteLine("DriveLetter : " + sDriveLetter);

}

EndStepDT = DateTime.Now;
StepRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;

Console.WriteLine(" Enum Runtime: {0, 8} ms", EnumRuntimeMs);
Console.WriteLine(" Step Runtime: {0, 8} ms", StepRuntimeMs);

StartStepDT = DateTime.Now;
}

Console.WriteLine("");

DateTime EndDT = DateTime.Now;

double RuntimeMs = (EndDT - StartDT).TotalMilliseconds;

Console.WriteLine("");
Console.WriteLine("Runtime: {0, 8} ms", RuntimeMs);

}
}
}
-----------------------------------------------------------------------------------------------------

Since this runs really reliable in an command shell i'm tempted to get the data using xp_cmdshell and a 6 KB console app.

Unless of course you know a way to get to get 2 things workin; mountpoint enumeration and getting size, free and volumename?

Grtz, t.
Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12065 Visits: 3290
Theo Ekelmans (2/25/2015)
By "But only to find out that .NET SQL CLR is "some what limited" in the things you can use." i ment that i got errors when i tried to use this construct in the CLR

ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");
ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
ManagementObjectCollection queryCollection = searcher.Get();

It seems that ManagementObjectSearcher is .NET 2 based an not allowed in a CLR.

It would have solved my performance problem, because if i use it in my console app, it rips through all 139 mountpoints in 500 ms flat; hundreds of times faster then using the perfmon counter workaround.

Since this runs really reliable in an command shell i'm tempted to get the data using xp_cmdshell and a 6 KB console app.


Ok, so here's the dealio:

.NET 2.0 has nothing to do with it. The problem with your original plan is that those classes (ManagementScope, ManagementObjectSearcher , etc.) are in the System.Management DLL, and that is not in the Supported .NET Framework Libraries list. So you have two options:

  • Manually load System.Management.dll via CREATE ASSEMBLY. This will require you to most likely create it as UNSAFE, which in turn will require that your custom assembly be created as UNSAFE. And that will require you to also set the database to TRUSTWORTHY ON in order to get the Microsoft DLL to load as UNSAFE. Since you don't know what exactly is going on in the Microsoft DLL, it could be doing things that are fine for a single-threaded process but exhibit odd behavior in a shared environment such as SQLCLR. This is due to there being a single, shared App Domain (per database, per owner) in SQL Server. If the code was not expecting multiple users/processes (i.e. Sessions / SPIDS) to share static, class-level variables then there might be issues. So it just requires a bit of extra testing, but can certainly be done. I would recommend using a non-user-facing instance dedicated to internal maintenance jobs. I think SQL Server Express is great for this. I don't think it comes with SQL Agent, but it can be connected to via a Linked Server from an instance that is Standard or Enterprise and a SQL Agent job on one of those could exec a proc on the Express instance over a Linked Server rather easily. This set up would isolate any risk of instability of the UNSAFE code. HOWEVER, this won't work if System.Management is a "mixed" Assembly and not "pure" MSIL. And even if it is today, Microsoft might change it to "mixed" in the future which will mean that it won't be loadable into SQL Server and you will be forced to recode this functionality. That might never happen, but it could, and I have seen one question on Stack Overflow due to someone experiencing this very thing. The next Level of my Stairway to SQLCLR series covers all of this (should be published in 2 weeks).


  • Use xp_cmdshell to run your console app. Using this method I would suggest formatting the output of the console app to be in XML. This should allow you to grab it from the result set ouptut of xp_cmdshell and then easily parse it using the built-in XML functions. I don't know if there is a maximum size of output that xp_cmdshell can handle, but if there is and this is too much, just have the console app save the XML to a file. And to make things a little safer, use the .NET method for generating a temp file name (in the File class I believe) and return that as output from the console app. Grab that temp file name via the result set output and then open that in your proc and get the XML. Of course, getting the file will require creating a SQLCLR function to File.ReadAllLines() or something like that, but that is supported and only requires EXTERNAL_ACCESS for the assembly. And since you can sign the assembly, you can create an Asymmetric Key from that DLL, a Login based on that Key, and then grant that Login the "EXTERNAL ACCESS ASSEMBLY" permission, all of which will allow you to set the Assembly to EXTERNAL_ACCESS without having to set the database to TRUSTWORTHY ON.


  • I hope this helps.

    Take care,
    Solomon...

    SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
    Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
    Theo Ekelmans
    Theo Ekelmans
    Hall of Fame
    Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

    Group: General Forum Members
    Points: 3630 Visits: 880
    Hell yeah Solomon, that helped indeed!!

    And as we say in Holland; "you do your name proud", i'm definitely going to try to manually load System.Management.dll into the assembly, because that one that contains all the mountpoint info i need. The unsafe status is not an issue here, because the DB it's being used in is my own SqlManagement DB, so i'm the only user of it.

    As for the suggestion of 'single treadedness', i can confirm that. If i run 2 console apps at the same time they both take much longer to complete.

    When i cut down all my console apps / processes accessing Win32_Volume to just ONE at a time, i got much better runtimes.

    That being said, i still have at random interval much longer runtimes. Which is probably my (single) console app or process interfering with SCOM which is probable also trying to use the same system.management.dll.

    Knowing this, i'm rebuilding my stored proc that is called by all sorts of code in my management tool on demand, to a process that runs once a minute that updates a table using Win32_Volume that all other processes can access at will, thus cutting down the chances of hitting a busy Win32_Volume thread :-)

    And if for some reason the System.Management.dll in my CLR project does not work, your other suggestion of just calling the 6 KB console app from xp_cmdshell, is the next best thing. I think padded columns is the easiest: just a 7 substrings per returned row.

    Somthing like this:

    Select
    cast(substring(outputstring, 1,25) as bigint) as var1,
    cast(substring(outputstring,26,25) as bigint) as var2,
    ..etc..
    from xpCmdShellOutput

    Once i've tested all of the above options and the chosen one it is stable on my most challenging prod environment (3 way stretched cluster with 7 instances per node with 139 mountpoints) i will post my source code here for anyone else struggeling with this scale / stability problem.

    Thanks again Solomon, and if we ever do meet in the flesh, i will most definitely buy you a drink :-D
    Solomon Rutzky
    Solomon Rutzky
    SSChampion
    SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

    Group: General Forum Members
    Points: 12065 Visits: 3290
    Theo Ekelmans (2/27/2015)
    Hell yeah Solomon, that helped indeed!!

    And as we say in Holland; "you do your name proud", i'm definitely going to try to manually load System.Management.dll into the assembly, because that one that contains all the mountpoint info i need. The unsafe status is not an issue here, because the DB it's being used in is my own SqlManagement DB, so i'm the only user of it.

    As for the suggestion of 'single treadedness', i can confirm that. If i run 2 console apps at the same time they both take much longer to complete.

    When i cut down all my console apps / processes accessing Win32_Volume to just ONE at a time, i got much better runtimes.


    Thanks :-). But honestly, I was only speaking in terms of SQL Server's CLR host / SQLCLR in terms of thread-safety issues. There is a single App Domain used for all sessions / SPIDs accessing a particular Assemby in SQL Server so static, class-level variables are shared among them. Console apps and Windows apps use a separate App Domain per each instance of those apps so static, class-level variables are not shared across users / processes / instances of the app. So, I was just warning that IF your code could be called by more than one Session at a time, that could certainly be prone to error, though not guaranteed to be a problem (hence the additional testing needed).

    Again, the main concern for loading System.Management.dll is, even if it loads now, Microsoft can change it to a mixed (MSIL and C++) assembly, making it unloadable into SQL Server, which means you would have to scrap this particular SQLCLR code. Of course, that day may never come, but it is a risk to factor into any decision making process.

    Regarding the xp_cmdshell idea: I generally use SQLCLR as a means of avoiding xp_cmdshell, but in this particular case it does remove that risk of System.Management.dll potentially not working in the future.

    Also, while doing 7 substrings should work every time, there is always a chance of something going wrong with text parsing. Using XML as the format at least removes one point of failure from the process. Slightly bulkier, but a lot more reliable / less brittle.

    Thanks again Solomon, and if we ever do meet in the flesh, i will most definitely buy you a drink :-D


    No problem. Glad I could help and I appreciate the offer :-D.

    Take care, solomon...

    SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
    Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
    Solomon Rutzky
    Solomon Rutzky
    SSChampion
    SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

    Group: General Forum Members
    Points: 12065 Visits: 3290
    Theo, I just found something that might help.

    Is it safe to say that you are only checking mount points where you currently have data and/or log files? These are the ones that you need to know if they are running out of space, right? As opposed to ALL mount points?

    If just the ones that have DB files on them, check out the sys.dm_os_volume_stats DMV (introduced in SQL Server 2008 R2).

    You can try the following query (assuming you are using SQL Server 2008 R2 or newer):

    SELECT files.*, '---' AS [---], stat.*
    FROM sys.[master_files] files
    CROSS APPLY sys.dm_os_volume_stats(files.[database_id], files.[file_id]) stat;



    SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
    Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
    Theo Ekelmans
    Theo Ekelmans
    Hall of Fame
    Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

    Group: General Forum Members
    Points: 3630 Visits: 880
    Hi Solomon,

    Again... you do you name justice Smile

    Considering that i'm usually only interested in drives that belong to the instance the query is actually running on, this query will do for the mountpoints that actually hold DB files.


    SELECT distinct volume_mount_point, logical_volume_name, total_bytes, available_bytes
    FROM sys.[master_files] files
    CROSS APPLY sys.dm_os_volume_stats(files.[database_id], files.[file_id]) stat
    order by volume_mount_point



    Worst case workaround, is to place a dummy DB on the drives/mountpoints that have no normal database... <shivers>, which is a nasty workaround that i would rather like to avoid

    .....<pondering>..... But considering that at this moment i do not have mountpoints that do not contains databases, i will rebuild my stored proc to optionally use this method.

    But in the mean time (and for SQL 2008 R2 and up), i will use this script to join the sys.dm with xp_fixeddrives for a reasonably complete list of all drives in a server instance.


    /**************************************************************************************
    Free and total bytes per drive / mountpoint
    ***************************************************************************************
    BEWARE: only mountpoints are shown that have active database files on them
    ***************************************************************************************
    Versie : 1.0
    Autheur : Theo Ekelmans / Solomon Rutzky
    Datum : 2015-04-14

    ***************************************************************************************
    enable OLA if needed
    ***************************************************************************************
    exec sp_configure 'show advanced options', 1
    reconfigure

    exec sp_configure 'Ole Automation Procedures', 1
    reconfigure
    ***************************************************************************************/
    SET NOCOUNT ON

    DECLARE @hr int
    DECLARE @fso int
    DECLARE @drive char(1)
    DECLARE @odrive int
    DECLARE @TotalSize varchar(20)
    DECLARE @MB bigint ; SET @MB = 1048576

    CREATE TABLE #drives (drive char(1) PRIMARY KEY,
    FreeSpace bigint NULL,
    TotalSize bigint NULL)

    --Get the "normal drives"
    INSERT #drives(drive,FreeSpace)
    EXEC master.dbo.xp_fixeddrives

    UPDATE #drives
    SET FreeSpace=FreeSpace * @MB

    -- update the list with the total size
    EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    DECLARE dcur CURSOR LOCAL FAST_FORWARD
    FOR SELECT drive from #drives
    ORDER by drive

    OPEN dcur

    FETCH NEXT FROM dcur INTO @drive

    WHILE @@FETCH_STATUS=0
    BEGIN

    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

    UPDATE #drives
    SET TotalSize=@TotalSize
    WHERE drive=@drive

    FETCH NEXT FROM dcur INTO @drive

    END

    CLOSE dcur
    DEALLOCATE dcur

    EXEC @hr=sp_OADestroy @fso
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    -- Put the output in temp table
    SELECT cast(drive + ':\' as varchar(128)) as [drive]
    ,cast('' as varchar(128)) as [VolumeName]
    ,FreeSpace as [Free]
    ,TotalSize as [Total]
    into #out
    FROM #drives

    DROP TABLE #drives

    --Merge the results with sys.dm_os_volume_stats
    update #out
    set VolumeName = mp.VolumeName
    ,Free = mp.Free
    ,Total = mp.Total
    from #out inner join ( SELECT distinct volume_mount_point as [drive], logical_volume_name as [VolumeName], available_bytes as [Free], total_bytes as [Total]
    FROM sys.[master_files] files
    CROSS APPLY sys.dm_os_volume_stats(files.[database_id], files.[file_id]) stat) as mp
    on #out.drive = mp.drive

    insert into #out
    SELECT distinct volume_mount_point as [drive], logical_volume_name as [VolumeName], available_bytes as [Free], total_bytes as [Total]
    FROM sys.[master_files] files
    CROSS APPLY sys.dm_os_volume_stats(files.[database_id], files.[file_id]) stat
    where volume_mount_point not in ( select [drive] FROM #out)

    --Show combined results
    select *
    from #out

    --Cleanup
    drop table #out




    Thanks again Smile
    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