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,
# 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
# Created by - Vinoth N Manoharan
# Version 1.1
# Date - 15/09/2011
# Script Help :-
#---------------
# Parameter 1 :- "-s" to run powershell for Single Instance of SQL
# "-f" to give filename with list of SQL Servers(Please provide SQL server name with instance details like <servername\instancename>
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- FindDBFile.ps1 -s <SQLservername\instancename>
# Example2:- FindDBFile.ps1 -f <filename with fully define path like c:\test.txt>
#
Clear-Host
#Write-Host "Num Args:" $args.Length;
$List =@()
if($args.Length -ne 2)
{
Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"
}
elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))
{
$computer = $args[1]
if($computer -ne $null)
{
$srt = "Server Name :- " + $computer
$srt
Echo "---------------------------"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"
$dbsall = $srv.Databases
#$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}
$dbs = $dbsall|Where {$_.IsAccessible}
foreach($db in $dbs)
{
$dbname = $db.Name
$dt = new-object "System.Data.DataTable"
$cn = new-object System.Data.SqlClient.SqlConnection "server=$computer;database=$dbname;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$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"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
#$dt
$List += $dt
}
$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|
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
$List| ft DBNAME, FILETYPE, NAME, FILENAME, FILE_SIZE_MB, SPACE_USED_MB, FREE_SPACE_MB, PERCT_USED_SPACE, GROWTH, MAXSIZE, DRIVE, DriveSizeGB, DriveFreeSpaceGB -AutoSize
Echo "`n"
}
}
elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))
{
$filename = $args[1]
$computers = get-content $filename
$List = @()
foreach ($computer in $computers)
{ $List = $null
if($computer -ne $null)
{
$srt = "Server Name :- " + $computer
$srt
Echo "---------------------------"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"
$dbsall = $srv.Databases
$dbs = $dbsall|Where {!$_.IsSystemObject -and $_.IsAccessible}
foreach($db in $dbs)
{
$dbname = $db.Name
$dt = new-object "System.Data.DataTable"
$cn = new-object System.Data.SqlClient.SqlConnection "server=$computer;database=$dbname;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$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"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
#$dt
$List += $dt
}
$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|
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
$List| ft DBNAME, FILETYPE, NAME, FILENAME, FILE_SIZE_MB, SPACE_USED_MB, FREE_SPACE_MB, PERCT_USED_SPACE, GROWTH, MAXSIZE, DRIVE, DriveSizeGB, DriveFreeSpaceGB -AutoSize
Echo "`n"
}
}
}else
{
Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"
}
