Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

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,


# 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
 
$Listft 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
 
$Listft 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"
}

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...