I created a small script to collect SQL Server file utilization data into a repository. I’m collecting the space used by the files as well as the space used internally by SQL Server. I’m doing this across all production databases and servers, so the T-SQL fileproperty function was not all that useful since it only works against the current database. The solution was to use SMO in a PowerShell script to collect the data since it’s very easy to iterate across multiple servers and databases. I’ll write up the solution at a later date, but for now I wanted to mention a minor fact that I learned today.
The SMO Database class has a Status property which returns at least two values, “Normal” and “Offline”. You can use it to avoid trying to get info for offline databases. For example:
if ( $db.status -eq “Normal”) { #exclude offline databases
#do something
}
