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


getting total disk space


getting total disk space

Author
Message
ramyours2003
ramyours2003
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 2922
i want to find the total drive space in server instaed of free space , can any one send the script ?
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
Powershell will probably be the best bet on that


Get-WmiObject win32_logicaldisk | where-Object {$_.providername -like ''} | select deviceid, size





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Mani Dev
Mani Dev
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 700
DECLARE @sqlver sql_variant

DECLARE @sqlver2 varchar(20)

DECLARE @sqlver3 int



SELECT @sqlver = SERVERPROPERTY('productversion')

SELECT @sqlver2 = CAST(@sqlver AS varchar(20))

select @sqlver3 = SUBSTRING(@sqlver2,1,1)



-- 1 = 2008 8 = 2000 and 9 = 2005 1 is short for 10

BEGIN

--select @sqlver3 only uncomment to see state of version

IF @sqlver3 = 1 GOTO SERVER2008

IF @sqlver3 = 9 GOTO SERVER2000

IF @sqlver3 = 8 GOTO SERVER2000

GOTO THEEND

END



SERVER2008:

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = @@SERVERNAME

set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table

CREATE TABLE #output

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert #output

EXEC xp_cmdshell @sql



--script to retrieve the values in GB from PS Script output

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drive

,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace'

,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'totalspace'

,((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)) * 100) as percentfree

from #output

where line like '[A-Z][:]%'

--and ((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

-- (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) / (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

--(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)) * 100) < 5

order by drive

--script to drop the temporary table

drop table #output

GOTO THEEND



SERVER2000:

SET NOCOUNT ON;



DECLARE @v_cmd nvarchar(255)

,@v_drive char(99)

,@v_sql nvarchar(255)

,@i int



SELECT @v_cmd = 'fsutil volume diskfree %d%'

SET @i = 1



CREATE TABLE #drives(iddrive smallint ,drive char(99))

CREATE TABLE #t(drive char(99),shellCmd nvarchar(500));

CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));



-- Use mountvol command to

INSERT #drives (drive)

EXEC master..xp_cmdshell 'mountvol'

DELETE #drives WHERE drive not like '%:\%' or drive is null



WHILE (@i <= (SELECT count(drive) FROM #drives))

BEGIN

UPDATE #drives

SET iddrive=@i

WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)



SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i



INSERT #t(shellCmd)

EXEC master..xp_cmdshell @v_sql



UPDATE #t

SET #t.drive = d.drive

FROM #drives d

WHERE #t.drive IS NULL and iddrive=@i



SET @i = @i + 1

END



INSERT INTO #total

SELECT bb.drive

,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))

,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace

,tt.titi as total

FROM #t bb

JOIN (SELECT drive

,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))

,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi

FROM #t

WHERE drive IS NOT NULL

AND shellCmd NOT LIKE '%free bytes%') tt

ON bb.drive = tt.drive

WHERE bb.drive IS NOT NULL

AND bb.shellCmd NOT LIKE '%avail free bytes%'

AND bb.shellCmd LIKE '%free bytes%';



-- SET FreespaceTimestamp = (GETDATE())

SELECT RTRIM(LTRIM(drive)) as drive

,freespace

,totalspace

,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]

FROM #total

--WHERE (freespace/totalspace * 100) < 5

ORDER BY drive



DROP TABLE #drives

DROP TABLE #t

DROP TABLE #total





THEEND:
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
anthony.green (1/22/2013)
Powershell will probably be the best bet on that


Get-WmiObject win32_logicaldisk | where-Object {$_.providername -like ''} | select deviceid, size


Win32_LogicalDisk will work in most cases but know that it does not pickup mount points. For a more comprehensive view (post Windows 2000 Server) use the Win32_Volume class instead.

Get-WmiObject Win32_Volume | gm


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
why cant we use "xp_fixeddrive" ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
Bhuvnesh (1/26/2013)
why cant we use "xp_fixeddrive" ?

I suppose you could. I haven't used it since the SQL 2000 days and if what I am reading online is correct however it still does not report on mount points.

http://connect.microsoft.com/SQLServer/feedback/details/301832/listing-mount-point-information-from-within-sql-server

Personally I prefer to do this kind of work outside SQL Server, and have for quite a long time. With a single PowerShell script running on one my 'food court' database server I check available space on all the servers across my environment and store the results in a database. I could do alerting from there as well but I haven't had to. I try to leave that to monitoring tools like SCOM or SQLdm.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 5351
Can't agree more opc. Powershell's a bit of a cow to learn but it's so useful I'd recommend it to all DBA's. Great tool to have in your utility belt!
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
Gazareth (1/26/2013)
Can't agree more opc. Powershell's a bit of a cow to learn but it's so useful I'd recommend it to all DBA's. Great tool to have in your utility belt!

:-P It sounds like you have done some work with it but I am not sure I agree with cow simile. Admittedly it's hard for me to convey a fresh perspective because I started playing with PowerShell ~3 years ago and have never looked back. If I never write another Windows batch or VBScript file again it will be too soon. That said, I only got serious about learning it and applying it in a structured way within the last year or so. I am reading this book now:

Learn Windows PowerShell 3 in a Month of Lunches by Don Jones and Jeffrey Hicks (aka Professor PowerShell

I am about 1/3 of the way through and it has already filled in many knowledge gaps left around by learning on my own and through repurposing bits of scripts I found on the net. The book focuses on using PowerShell to accomplish real tasks right away rather than treating it as a scripting language you have to learn about from end-to-end before you can do anything useful. The other great thing it does is teach you how to fish, and what I mean by that is it teaches you how to use the help system built into PowerShell which is pretty robust and user-friendly such that once you learn it you can move around and pickup new things quite easily.

The book does have a "system administrator" spin as that is its core audience but we can't kid ourselves, most DBAs have to wear that hat all too often so learning PowerShell through that lense is not a stretch at all. I am easily able to apply everything I have learned so far when using the SQL Server PowerShell provider.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45151 Visits: 39923
mani@123 (1/22/2013)
SERVER2008:

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = @@SERVERNAME

set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table

CREATE TABLE #output

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert #output

EXEC xp_cmdshell @sql


+10000000000000000000000 Mani!!! I absolutely freakin' love it! :-) I was starting to think I was the only one in the world that did that!

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
OPC is correct, XP_FIXEDDRIVES cannot enumerate mounted volumes. A straight WMI call will also work using

wmic volume get capacity, "free space", name



You can also supply an output file and remote node names too

wmic /output:"c:\temp\mountsizes.txt" /node:sqlnode1,sqlnode2,sqlnode3,sqlnode4
volume get capacity, "free space", name



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
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