Collect Database File Size with Powershell

I decided to start monitoring the amount of free space in my database files so that I can make sure all my databases are set to auto grow by the right amount and to monitor the amount of free space so I can schedule the expansion of the files rather than let them auto grow.  I still plan to allow the files to auto grow if needed but by properly monitoring the size this will become the exception and not the norm.

I am certain there are numerous tools out there that will handle this for me but today I don’t have those tools.  What I do have is SSMS, Powershell, and a bunch of servers.  I put together a simple query that uses sp_MSforeachdb to gather the information I needed from the entire instance.  For now I am just gathering the Instance Name, database name, fileid, auto grow size in mb, file size in mb, space used in mb, free space in mb, percent free in mb, file name, file path, and the current time.

Running this query against each instance was easy enough but I have over 100 instances.  I don’t want to schedule a job on each server to run this and I haven’t implemented Central Management Server yet so I turned to Powershell to make this happen.  I recalled a conversation I had recently with a good friend Jon Boulineau (blog) where he was collecting database backup information from a list of servers, so I pinged him for advise.  While having an IM chat one night with Jon I was telling him what I was looking to do and within a few minutes Jon sent me a Posh script to model mine after.

Jon is one of the best developers that I know and the script he sent me was very well laid out and already had just about everything I needed minus my query.

I am really looking forward to putting this to use and building upon it.  I know there are several packs of Posh scripts that are available for SQL Server management and I will be looking at them shortly after the Summit.

$listOfServers = "server1", "server2", "server3"            
 $query = "sp_MSforeachdb  'use [?] 
select @@servername AS ServerName
 
,DB_NAME() AS DB_NAME

,[fileid]
 
,GROWTH = CASE WHEN [growth] = ''10'' THEN convert(nvarchar(10),''10%'') ELSE convert(nvarchar(10),[growth]/128) END 
,
[File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2)) 

,[Space_Use_MB] = convert(decimal(12,2),round(fileproperty([name],''SpaceUsed'')/128.000,2))
 
,[Free_Space_MB]=convert(decimal(12,2),round(([size]-fileproperty([name],''SpaceUsed''))/128.000,2)) 

,[Percent_Free_MB]=cast((([size]-fileproperty([name],''SpaceUsed''))/128.00000)/([size]/128.000) as numeric(10,2))*100 

,RTRIM(LTRIM([name])) AS FileName
 
,RTRIM(LTRIM([filename])) AS FilePath 

,convert(datetime,getdate(),112) as DateInserted 
FROM dbo.sysfiles'"             
$destinationServerName = "reporting_server"            
$destinationDatabaseName = "db_name"            
foreach($server in $listOfServers)            
 {                    
    $results = Invoke-Sqlcmd -ServerInstance $server -Query $query            

    $connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;                    
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;                    
    $bulkCopy.DestinationTableName = "TableName" ;             
    $bulkCopy.WriteToServer($results) ;             
}