April 28, 2011 at 9:46 am
Anybody knows how to get the mount point usage using sql server 2005/2008 command?
Thanks in advance!
----------
I got a vbs script for the same request - but if somebody knows a t-sql script, please let me know.
strComputer = "."
Set objWMIService = GetObject _
( "winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery _
("Select * From Win32_Volume Where DriveType = 3" )
For Each objItem In colItems
iSpc = cDbl(objItem.FreeSpace)
ispc1 = cDbl(objItem.Capacity)
WScript.Echo objItem.Label, "Drive", "has", FormatiSpc(iSpc) & "GB Free OF Volume size: " & FormatiSpc(iSpc1) & "GB"
Next
Function FormatiSpc(intSpace)
intSpace = intSpace/1024
intSpace = intSpace/1024
intSpace = intSpace/1024
intSpace= FormatNumber(intSpace,1)
FormatiSpc = intSpace
end Function
April 28, 2011 at 10:30 pm
As this is an operating system consideration, there is no direct way to get it through a T-SQL query, other than using xp_cmdshell or a CLR function.
I'd suggets writing a CLR function to do this as .NET will expose the drives and mountpoints more cleanly and you don't have the same security issues as running VBScript.
I assume you want this as a dynamic "live" query? If a daily snapshot is good enough then running a powershell script to populate a table or something similar would also work.
April 29, 2011 at 5:34 am
If you are using SQL2008 where powershell script is supported and if your SQL Server service account has enough privilege on the mount points, you can schedule powershell scripts to collect disk space information.
And this approach is being used right now by my company and myself. Another word of advice, the underlying OS has to be Win2003 or newer to fully support powershell scripts. Based on my own experience, I failed to retrieve disk space info on mount points in Win2000 servers by powersehll.
Bazinga!
April 29, 2011 at 8:17 am
Thanks a lot for your fast reply.
I believe a sql query is there to retrieve the information.
April 29, 2011 at 11:44 am
I use below T-SQL code to get mount point that are used by databases from sysaltfiles.
In below code, you need to change following lines as per your enviornment.
select distinct substring(filename, 1, charindex('database',filename,0)-1) from master..sysaltfiles
where dbid > 4 and groupid = 1 and filename not like '%Log%' and filename not like '%SystemDB%'
union
select distinct substring(filename, 1, charindex('dblogs',filename,0)-1) from master..sysaltfiles
where dbid > 4 and groupid = 0 and filename not like '%AppDB%' and filename not like '%SystemDB%'
set nocount on
declare @Objname nvarchar(50), @dbname nvarchar(100), @ToDbname nvarchar(100),
@Fserver nvarchar(50), @Tserver nvarchar(50),
@Org_datafile nvarchar (260) , @To_datafile nvarchar(260),
@Org_Logfile nvarchar(260), @To_logfile nvarchar(260),
@cmd nvarchar(2000), @quote_double char(1), @return_code int,
@mount_point_name nvarchar(1000), @free_space int, @total_space int,
@result nvarchar(4000)
create table #mountpoint_space_query (
space_output nvarchar(2000)
)
create table #mountpoint_space_result (
mount_point_name varchar(500),
space_free_MB int,
space_total_MB int,
)
DECLARE SpaceCursor CURSOR FOR
--select DISTINCT
--RTRIM(LTRIM(left(filename,len(filename) - charindex('database',filename,0)+1 )))
--from master..sysaltfiles
--where groupid = 1 and dbid > 4
--
--select
--charindex('database',filename,0)-1, filename, *
--from master..sysaltfiles
--where groupid = 1 and dbid > 4
select distinct substring(filename, 1, charindex('database',filename,0)-1) from master..sysaltfiles
where dbid > 4 and groupid = 1 and filename not like '%Log%' and filename not like '%SystemDB%'
union
select distinct substring(filename, 1, charindex('dblogs',filename,0)-1) from master..sysaltfiles
where dbid > 4 and groupid = 0 and filename not like '%AppDB%' and filename not like '%SystemDB%'
open SpaceCursor
FETCH NEXT FROM SpaceCursor INTO @result
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
truncate table #mountpoint_space_query
--PRINT @result
--set @cmd = 'xp_cmdshell ''dir ' + @result + ''''
SET@cmd = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + @result + ''''
--PRINT @cmd
insert into #mountpoint_space_query ( space_output) exec(@cmd)
--select @free_space=cast (replace(substring(space_output, charindex('Dir(s)',space_output,0)+6, charindex('bytes free',space_output,0)- charindex('Dir(s)',space_output,0) -6), ',', '') as bigint )/(1024*1024)
--from #mountpoint_space_query
--where space_output like '%bytes free%'
select @free_space = cast(substring(space_output, charindex(':',space_output,0)+1, 255) as bigint)/(1024*1024) from #mountpoint_space_query
where space_output like '%Total # of free bytes%'
select @total_space = cast(substring(space_output, charindex(':',space_output,0)+1, 255) as bigint)/(1024*1024) from #mountpoint_space_query
where space_output like '%Total # of bytes%'
insert into #mountpoint_space_result (mount_point_name, space_free_MB, space_total_MB)
values (@result, @free_space, @total_space)
END
FETCH NEXT FROM SpaceCursor INTO @result
END
close SpaceCursor
deallocate SpaceCursor
--select * from #mountpoint_space_result
select @@servername, *, cast(((cast(space_free_MB as float))/cast(space_total_MB as float)*100) as float) as [PercentFree] from #mountpoint_space_result
--where cast(((cast(space_free_MB as float))/cast(space_total_MB as float)*100) as float) < 20
drop table #mountpoint_space_query;drop table #mountpoint_space_result;
February 23, 2012 at 1:20 am
Could you explain a little bit more what you mean about setting the values according to 'your' environment? What do you mean, and which values do you change? Why are there so many lines commented out in the middle of the script?
I appreciate if you can take the time to answer this for me.
Thanks,
Greg
April 12, 2012 at 10:24 pm
while applying the script giving errors like below
Msg 536, Level 16, State 5, Line 36
Invalid length parameter passed to the SUBSTRING function.
Msg 16917, Level 16, State 2, Line 37
Cursor is not open.
Msg 16917, Level 16, State 1, Line 64
Cursor is not open.
how we can fix the errors...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply