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
SQL Server Database Size and Free Disk Space Daily Report
Powershell Scripts for DBA - SQL Server DBA
Powershell to HTML - SQL Server DBA
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |