Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

SQL Database Size report using Powershell

02 April,2013 by Tom Collins

Question: Could you supply a Powershell script to  report all SQL Server database sizes  across the environment , broken down in Data  and Logs? This report must run on a regular , on-demand basis.

Answer:  Yes , it’s possible by running a t-sql script across a list of SQL Server Instances using Powershell.   

As long as the instances list is up to date , executing this script will iterate through every SQL Server Instance – execute the t-sql code , and create a html file with the naming convention -  for example : sql_server_health_db_sizes_2013-04-02T153128.html

 You may need to customise the locations :

Line 1 - set-location => the current location of the powershell script

Line 8 - $instancepath=$basepath + "\config\instances.txt" =>  the list of SQL Server instances 

Line 9 - $outputfile=   => place the location of the output file 

 

set-location E:\health\SQL_Server

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$instancepath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_db_sizes_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "
select @@servername as ServerName,
DB_NAME(mf.database_id) as [db_name],
type_desc,
str(convert(dec(14,2),size) / 128,14,2) as DATABASE_SIZE_MB,
str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Database Sizes" | Set-Content $outputfilefull  

$filepath = $outputfilefull  

 Read More

SQL Server Database Size and Free Disk Space Daily Report

Powershell Scripts for DBA - SQL Server DBA

Powershell to HTML - SQL Server DBA


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Database Size report using Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer