Gathering Database sizing information

So I have been capuring my growth data for quite some time using linked servers and sql code.  It has been working and giving me the information that I needed so I was happy. 

The server that carries my database of information needed to be retired.  Presto, chango no problem I moved my data to a new server with a newer verion of sql server and I'm happy.  However, I really did not want to have a lot of linked server connections around just so I could gather my data into my new server.  In comes PowerShell. Da-ta-da!!

I have been working with Powershell on some active directory work and so have gained some knowledge of it.  I know I can be doing more with it on the database side of life and decided this was a good opportunity.  So now I gather my data with a couple of powershell scripts.

I use one to gather information about filesizes and another gather the physical disk free space.
So I looked at what some other folks are doing with powershell and compared that with what I was doing with sql server and merged those ideas.  Actually with the snapins and sqlcmd it was not a very big switch.  I used Tim Radney's concept of passing in the listofservers and stuck in my table data.  Ed Wilson and Dale Kelley look to be using a file for this input and I am going to look into that.

For the server freespace:
Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100
#
$listOfServers = "a", "b", "c","d", "e"
$query = "
create table #FreeSpace (partition char(1), mb_free int)
insert into #FreeSpace exec xp_fixeddrives
select null,@@Servername, partition,mb_free from #FreeSpace"
$destinationServerName = "servername"
$destinationDatabaseName = "Databasename"
foreach($server in $listOfServers)
{
$results = Invoke-Sqlcmd -ServerInstance $server -Query $query
Write-Host "Arg: $results"
$results
$connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;
$bulkCopy.DestinationTableName = "DatabaseTableName" ;
$bulkCopy.WriteToServer($results) ;
}

For the file size data:
Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100
#
$listOfServers = "a", "b", "c","d", "e"
$query = "sp_MSforeachdb 'use [?]
select null,
@@servername AS ServerName
,DB_NAME() AS DatabaseName
,name
,filename
,(size*8)
,[File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2))
FROM dbo.sysfiles;'"
$destinationServerName = "servername"
$destinationDatabaseName = "Databasename"
foreach($server in $listOfServers)
{
$results = Invoke-Sqlcmd -ServerInstance $server -Query $query
Write-Host "Arg: $results"
$results
$connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;
$bulkCopy.DestinationTableName = "database_table_name" ;
$bulkCopy.WriteToServer($results) ;
}

I run it as a scheduled task and gather the data on a weekly basis.  For me it is working great.

Happy dba'ing and remember:

Help I'm a DBA...
there really is a lot of resource help out there.