Tuesday, 14 August 2012

Database and Disk Size - PowerShell Script

Every time we do a capacity analysis we need to analyse the database size (free and used) and the disk size in which the database is associated frequently,We can use Powershell to append both TSQL and WMI output to accomplish this task,
 
1:  # Script to find Database File(Data and Log)Space details, Autogrow setting, Drive space details where the DB resides on a given set of SQL server instances  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 15/09/2011  
5:  # Script Help :-  
6:  #---------------  
7:  # Parameter 1 :- "-s" to run powershell for Single Instance of SQL  
8:  # "-f" to give filename with list of SQL Servers(Please provide SQL server name with instance details like ervername\instancename;  
9:  # Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)  
10:  #  
11:  # Example1:- FindDBFile.ps1 -s <SQLservername\instancename>  
12:  # Example2:- FindDBFile.ps1 -f <filename with fully define path like c:\test.txt>  
13:  # 
 
14:  Clear-Host  
15:  #Write-Host "Num Args:" $args.Length;  
16:  $List =@()  
17:  if($args.Length -ne 2)  
18:  {  
19:  Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"  
20:  }  
21:  elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
22:  {  
23:  $computer = $args[1]  
24:  if($computer -ne $null)  
25:  {  
26:  $srt = "Server Name :- " + $computer  
27:  $srt  
28:  Echo "---------------------------"  
29:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
30:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
31:  $dbsall = $srv.Databases  
32:  #$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
33:  $dbs = $dbsall|Where {$_.IsAccessible}  
34:  foreach($db in $dbs)  
35:  {  
36:  $dbname = $db.Name  
37:  $dt = new-object "System.Data.DataTable"  
38:  $cn = new-object System.Data.SqlClient.SqlConnection "server=$computer;database=$dbname;Integrated Security=sspi"  
39:  $cn.Open()  
40:  $sql = $cn.CreateCommand()  
41:  $sql.CommandText = "select serverproperty('ComputerNamePhysicalNetBIOS') AS SERVERNAME,db_name() as DBNAME, CASE WHEN a.FILENAME LIKE '%.ldf' THEN 'LOG FILE' ELSE 'DATA FILE' END AS [FILETYPE],NAME = left(a.NAME,100),FILENAME = left(a.FILENAME,300),DRIVE = left(a.FILENAME,2) ,[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),[SPACE_USED_MB] =convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,[PERCT_USED_SPACE]= convert(decimal(12,2),(convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))/convert(decimal(12,2),round(a.size/128.000,2)))* 100),a.growth as GROWTH,a.maxsize as MAXSIZE from dbo.sysfiles a"  
42:  $rdr = $sql.ExecuteReader()  
43:  $dt.Load($rdr)  
44:  $cn.Close()  
45:  #$dt  
46:  $List += $dt  
47:  }  
48:  $List = $List|Add-Member -MemberType ScriptProperty -Name DriveSizeGB -Value {[math]::Round((((Get-WmiObject -ComputerName $this.SERVERNAME Win32_LogicalDisk | Where-Object {$_.DeviceID -eq$this.DRIVE}| Select Size).Size)/1GB),2)} -PassThru|  
49:  Add-Member -MemberType ScriptProperty -Name DriveFreeSpaceGB -Value {[math]::Round((((Get-WmiObject-ComputerName $this.SERVERNAME Win32_LogicalDisk | Where-Object {$_.DeviceID -eq $this.DRIVE}|Select FreeSpace).FreeSpace)/1GB),2)} -PassThru  
50:  $List| ft DBNAME, FILETYPE, NAME, FILENAME, FILE_SIZE_MB, SPACE_USED_MB, FREE_SPACE_MB, PERCT_USED_SPACE, GROWTH, MAXSIZE, DRIVE, DriveSizeGB, DriveFreeSpaceGB -AutoSize  
51:  Echo "`n"  
52:  }  
53:  }  
54:  elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))  
55:  {  
56:  $filename = $args[1]  
57:  $computers = get-content $filename  
58:  $List = @()  
59:  foreach ($computer in $computers)  
60:  { $List = $null  
61:  if($computer -ne $null)  
62:  {  
63:  $srt = "Server Name :- " + $computer  
64:  $srt  
65:  Echo "---------------------------"  
66:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
67:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
68:  $dbsall = $srv.Databases  
69:  $dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}  
70:  foreach($db in $dbs)  
71:  {  
72:  $dbname = $db.Name  
73:  $dt = new-object "System.Data.DataTable"  
74:  $cn = new-object System.Data.SqlClient.SqlConnection "server=$computer;database=$dbname;Integrated Security=sspi"  
75:  $cn.Open()  
76:  $sql = $cn.CreateCommand()  
77:  $sql.CommandText = "select serverproperty('ComputerNamePhysicalNetBIOS') AS SERVERNAME,db_name() as DBNAME, CASE WHEN a.FILEID = 2 THEN 'LOG FILE' ELSE 'DATA FILE' END AS [FILETYPE],NAME = left(a.NAME,100),FILENAME = left(a.FILENAME,300),DRIVE = left(a.FILENAME,2) ,[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),[SPACE_USED_MB] =convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,[PERCT_USED_SPACE]= convert(decimal(12,2),(convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))/convert(decimal(12,2),round(a.size/128.000,2)))* 100),a.growth as GROWTH,a.maxsize as MAXSIZE from dbo.sysfiles a"  
78:  $rdr = $sql.ExecuteReader()  
79:  $dt.Load($rdr)  
80:  $cn.Close()  
81:  #$dt  
82:  $List += $dt  
83:  }  
84:  $List = $List|Add-Member -MemberType ScriptProperty -Name DriveSizeGB -Value {[math]::Round((((Get-WmiObject -ComputerName $this.SERVERNAME Win32_LogicalDisk | Where-Object {$_.DeviceID -eq$this.DRIVE}| Select Size).Size)/1GB),2)} -PassThru|  
85:  Add-Member -MemberType ScriptProperty -Name DriveFreeSpaceGB -Value {[math]::Round((((Get-WmiObject-ComputerName $this.SERVERNAME Win32_LogicalDisk | Where-Object {$_.DeviceID -eq $this.DRIVE}|Select FreeSpace).FreeSpace)/1GB),2)} -PassThru  
86:  $List| ft DBNAME, FILETYPE, NAME, FILENAME, FILE_SIZE_MB, SPACE_USED_MB, FREE_SPACE_MB, PERCT_USED_SPACE, GROWTH, MAXSIZE, DRIVE, DriveSizeGB, DriveFreeSpaceGB -AutoSize  
87:  Echo "`n"  
88:  }  
89:  }  
90:  }else  
91:  {  
92:  Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"  
93:  }  

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

12 comments:

  1. Nice one but I have a problem with showing free space why??

    ReplyDelete
    Replies
    1. What's the problem you are having?

      Delete
  2. so does your script assume that one is running this script within sql server itself?
    The reason I ask is that it attempts to make use of smo.server type, but the script, as written, does not include any sql server related modules. When I attempt to run the code I get an error that says that the type cannot be found and that I need to make sure the assembly containing this type is loaded.

    ReplyDelete
    Replies
    1. No you should be able to run it from any machine which has SQL Client installed(SMOObjects), I did include the SMO objects to the script in line 29
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

      If you can send the error you are getting i can help you with it.

      Delete
  3. I am new to Powershell and I am trying to run your script. When I tried to run it as follows "FindDBFile.ps1 -s SQL2012ETEST" I get no output and no errors, nothing happens. What am I doing wrong?

    ReplyDelete
  4. Script not giving any output........how to execute?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Example1:- FindDBFile.ps1 -s SQLservername\instancename

      Delete
  5. It's runs beautiful except I'm not able to see the drive free space. Using it on win server 2012 and windows 8.1. ANy tips ? Thanks ;)

    ReplyDelete
  6. Hi, I need this in spreadsheet format, can you modify to reflect output into csv please?
    Thanks so much

    ReplyDelete