$svr = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server.instance_name
$version = $svr.version
foreach ($db in $svr.databases)
At the beginning of each loop we set the value of $dbsize and $logsize as these properties can be empty if the DB is unavailable. We then check the database status before we attempt to get the size properties. Because the $db.size property includes data and log files we take the sum of the size properties in the $db.logfiles collection and subtract it from the $db.size property. We also have to divide the log file size by 1024 because it is listed in KB not MB. Too bad there’s no datafiles collection!
$dbsize = "NULL"
$logsize = "NULL"
If ($db.status -match "Normal")
$dbsize = $db.size – ($db.logfiles|measure-object -property size -sum).sum / 1024
$logsize = ($db.logfiles|measure-object -property size -sum).sum / 1024
Now we create some variables for use in our INSERT statement. the $object.property variables didn’t seem to jive well with all the quotes, so it was easier to set up some base variables. We also set the date to a SQL datetime friendly format. And remove any commas from the $db.status property as our SQL INSERT statement won’t like those at all!
$svrname = $svr.name
$dbname = $db.name
$dbcreatedate = $db.createdate
$dbstatus = ($db.status).tostring()
$dbstatus = $dbstatus.Replace(","," ")
This next section is kind of meaning less for the script, but it’s wicked fun. The properties and variables we’ve created can be hard to format for output. The general PSObject object take care of this delimma for us. First we create a new object, $obj. Then we add each of our different database properties. Once we are done we add the object to our $DBInfo array. During each loop a new object is created and added to the array. You could use "$DBInfo | Format-Table" to more easily view the information, or "$DBInfo | ConvertTo-HTML | Out-File dbinfo.html" to make a quick web page. If your already have the web server up an automated daily job could overwrite the page so that interested parties can keep track of their database sizes. We also use this to see if new databases "pop-up" because of a wiley developer!
$obj | Add-Member NoteProperty -name ServerName -value $svr.name
$obj | Add-Member NoteProperty -name DatabaseName -value $db.name
$obj | Add-Member NoteProperty -name SQLVersion -value $version
$obj | Add-Member NoteProperty -name CreateDate -value $db.createdate
$obj | Add-Member NoteProperty -name DBSize -value $dbsize
$obj | Add-Member NoteProperty -name LogSize -value $logsize
$obj | Add-Member NoteProperty -name DBStatus -value $db.status
invoke-sqlcmd -serverinstance "yourserver" -query $sqlcmd