getting total disk space

  • i want to find the total drive space in server instaed of free space , can any one send the script ?

  • Powershell will probably be the best bet on that

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

  • 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:

  • 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

  • why cant we use "xp_fixeddrive" ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • 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

  • 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!

  • 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!

    😛 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

  • 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

    [font="Arial Black"]set @sql = 'powershell.exe [/font]-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

    [font="Arial Black"]EXEC xp_cmdshell @sql[/font]

    +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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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" 😉

  • opc.three (1/26/2013)


    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

    Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

  • anthony.green (1/29/2013)


    Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

    Check my article at this link[/url] for more info on mount points and SQL Server.

    The problem with mount points is that when you execute XP_FIXEDDRIVES it only enumerates the root drives and won't return information from the volume level. It's all detailed in my article, post back if you're unsure of the workings of it all.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (1/29/2013)


    anthony.green (1/29/2013)


    Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

    Check my article at this link[/url] for more info on mount points and SQL Server.

    The problem with mount points is that when you execute XP_FIXEDDRIVES it only enumerates the root drives and won't return information from the volume level. It's all detailed in my article, post back if you're unsure of the workings of it all.

    Thanks Perry, added to my list of further reading.

  • One really good powershell script is one a colleague sent me that emails me total disk space and free disk space for all servers listed in a text file:-

    Have a look 🙂

    # First lets create a text file, where we will later save the freedisk space info

    $freeSpaceFileName = "<LOCATION>"

    $serverlist = "<LOCATION>\Servers.txt"

    $warning = 20

    $critical = 10

    $date = Get-Date -format d

    New-Item -ItemType file $freeSpaceFileName -Force

    # Getting the freespace info using WMI

    #Get-WmiObject win32_logicaldisk | Where-Object {$_.drivetype -eq 3} | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt

    # Function to write the HTML Header to the file

    Function writeHtmlHeader

    {

    param($fileName)

    $date = Get-Date -format d

    Add-Content $fileName "<html>"

    Add-Content $fileName "<head>"

    Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"

    Add-Content $fileName '<title> $Date DiskSpace Report</title>'

    add-content $fileName '<STYLE TYPE="text/css">'

    add-content $fileName "<!--"

    add-content $fileName "td {"

    add-content $fileName "font-family: Tahoma;"

    add-content $fileName "font-size: 11px;"

    add-content $fileName "border-top: 1px solid #999999;"

    add-content $fileName "border-right: 1px solid #999999;"

    add-content $fileName "border-bottom: 1px solid #999999;"

    add-content $fileName "border-left: 1px solid #999999;"

    add-content $fileName "padding-top: 0px;"

    add-content $fileName "padding-right: 0px;"

    add-content $fileName "padding-bottom: 0px;"

    add-content $fileName "padding-left: 0px;"

    add-content $fileName "}"

    add-content $fileName "body {"

    add-content $fileName "margin-left: 5px;"

    add-content $fileName "margin-top: 5px;"

    add-content $fileName "margin-right: 0px;"

    add-content $fileName "margin-bottom: 10px;"

    add-content $fileName ""

    add-content $fileName "table {"

    add-content $fileName "border: thin solid #000000;"

    add-content $fileName "}"

    add-content $fileName "-->"

    add-content $fileName "</style>"

    Add-Content $fileName "</head>"

    Add-Content $fileName "<body>"

    add-content $fileName "<table width='100%'>"

    add-content $fileName "<tr bgcolor='#CCCCCC'>"

    add-content $fileName "<td colspan='7' height='25' align='center'>"

    add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"

    add-content $fileName "</td>"

    add-content $fileName "</tr>"

    add-content $fileName "</table>"

    }

    # Function to write the HTML Header to the file

    Function writeTableHeader

    {

    param($fileName)

    Add-Content $fileName "<tr bgcolor=#CCCCCC>"

    Add-Content $fileName "<td width='10%' align='center'>Drive</td>"

    Add-Content $fileName "<td width='50%' align='center'>Drive Label</td>"

    Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"

    Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"

    Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"

    Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"

    Add-Content $fileName "</tr>"

    }

    Function writeHtmlFooter

    {

    param($fileName)

    Add-Content $fileName "</body>"

    Add-Content $fileName "</html>"

    }

    Function writeDiskInfo

    {

    param($fileName,$devId,$volName,$frSpace,$totSpace)

    $totSpace=[math]::Round(($totSpace/1073741824),2)

    $frSpace=[Math]::Round(($frSpace/1073741824),2)

    $usedSpace = $totSpace - $frspace

    $usedSpace=[Math]::Round($usedSpace,2)

    $freePercent = ($frspace/$totSpace)*100

    $freePercent = [Math]::Round($freePercent,0)

    if (($freePercent -le $warning) -and ($FreePercent -gt $critical))

    {

    Add-Content $fileName "<tr>"

    Add-Content $fileName "<td align=center>$devid</td>"

    Add-Content $fileName "<td align=center>$volName</td>"

    Add-Content $fileName "<td align=center>$totSpace</td>"

    Add-Content $fileName "<td align=center>$usedSpace</td>"

    Add-Content $fileName "<td align=center>$frSpace</td>"

    Add-Content $fileName "<td bgcolor='#FFE600' align=center>$freePercent</td>"

    Add-Content $fileName "</tr>"

    }

    elseif ($freePercent -le $critical)

    {

    Add-Content $fileName "<tr>"

    Add-Content $fileName "<td align=center>$devid</td>"

    Add-Content $fileName "<td align=center>$volName</td>"

    Add-Content $fileName "<td align=center>$totSpace</td>"

    Add-Content $fileName "<td align=center>$usedSpace</td>"

    Add-Content $fileName "<td align=center>$frSpace</td>"

    Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"

    Add-Content $fileName "</tr>"

    }

    else

    {

    Add-Content $fileName "<tr>"

    Add-Content $fileName "<td align=center>$devid</td>"

    Add-Content $fileName "<td align=center>$volName</td>"

    Add-Content $fileName "<td align=center>$totSpace</td>"

    Add-Content $fileName "<td align=center>$usedSpace</td>"

    Add-Content $fileName "<td align=center>$frSpace</td>"

    Add-Content $fileName "<td align=center>$freePercent</td>"

    Add-Content $fileName "</tr>"

    }

    }

    Function sendEmail

    { param($from,$to,$subject,$smtphost,$htmlFileName)

    $body = Get-Content $htmlFileName

    $smtp= New-Object System.Net.Mail.SmtpClient $smtphost

    $msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body

    $msg.isBodyhtml = $true

    $smtp.send($msg)

    }

    writeHtmlHeader $freeSpaceFileName

    foreach ($server in Get-Content $serverlist)

    {

    Add-Content $freeSpaceFileName "<table width='100%'><tbody>"

    Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"

    Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server </strong></font></td>"

    Add-Content $freeSpaceFileName "</tr>"

    writeTableHeader $freeSpaceFileName

    $dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3}

    foreach ($item in $dp)

    {

    Write-Host $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

    writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

    }

    Add-Content $freeSpaceFileName "</table>"

    }

    writeHtmlFooter $freeSpaceFileName

    $date = Get-Date -format d

    sendEmail <SENDER EMAIL ADDRESS> <RECIPIENT EMAIL ADDRESS> "Disk Space Report - $Date" <SMTP SERVER> $freeSpaceFileName

  • Did you try this ?

    SELECT distinct

    vs.volume_mount_point

    , vs.volume_id

    , vs.logical_volume_name

    , vs.file_system_type

    , vs.total_bytes / 1048576 AS 'MBTotal'

    , vs.available_bytes / 1048576 AS 'MBAvailable'

    , CAST( 1.00 * vs.available_bytes / vs.total_bytes * 100 as decimal(5,2) ) as pctFree

    , vs.supports_compression

    , vs.supports_alternate_streams

    , vs.supports_sparse_files

    , vs.is_read_only

    , vs.is_compressed

    FROM [sys].[master_files] AS f

    CROSS APPLY [sys].[dm_os_volume_stats]([f].[database_id], [f].[file_id]) AS vs

    ORDER BY [vs].[logical_volume_name] ;

    Of course, this will only provide information about drives to which the sqlinstance has databases allocated.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply