The Multifunctioning DBA

Apr 13 2010   5:50PM GMT

Get a quick Idea of Space used for SQL

Colin Smith Colin Smith Profile: Colin Smith

A couple weeks ago I had my SAN admin ask me about how much disk I would need to backup all of my sql instances to the SAN. Well I do not know that off the top of my head but I do have a list of all my instances and I know a little bit about powershell. So I decided to throw together a quick and dirty script to connect to all instances, gather size information about all databases on those instances and get me a total. Here is what I did.



############################################################ ## DOC This script will create an email containing the ## DOC combined size of all databases in instances defined ## DOC in the sqltab.txt file. This will be used to estimate ## DOC the amount of SAN disk that we need for backups. ############################################################ #Read thru the contents of the SQL_Servers.txt file $size = 0 $outfile = "\\share\Size.txt" Clear-Content $outfile $servers = Import-Csv "\\sharename\input.txt" ######################################################### foreach ($entry in $servers) { $machine = $entry.server $iname = $entry.Instance $torp = $entry.TorP if ($torp -eq "Prod") { if ($iname -eq "Null") { $instance = "$machine" } else { $instance = "$machine\$iname" } $instance = $instance.toupper() #Connect to SQL Server and get database size information using SMO [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null # Create an SMO connection to the instance $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance if (!$?) { Echo "Error connecting to $instance, Please check" >> $outfile } $dbs = $s.Databases ## Get infomation on all dbs in instance. Add up all database sizes foreach ($db in $dbs) { $dbname = $db.Name $dbsize = $db.Size $size = $size + $dbsize Echo "$dbname on $instance is $dbsize MB" >> $outfile } } } $size = $size / 1048576 ## dividing by 1 TB so I can get my output in TB. $size = [math]::Round($size, 2) ## rounding number to get two decimal places. Echo "Total Size of all Production databases that I could connect to is $size TB." Echo "Total Size of all Production databases that I could connect to is $size TB." >> $outfile

I am using the same input file that I have written about in the past. It is a txt file that is comma seperated and here are the headers.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

I use this file for my scripted monitoring so you will not need all the fields that I have in the file for this script. Please let me know if you have any questions.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: