Here is a fairly simple PowerShell script that can be used to collect database file statistics across multiple servers. My version of the script is incorporated into a scheduled SQL Agent job that runs daily. It is very useful for trend analysis.
You will need two tables to support this script. The first table is simply a list of servers that you wish to monitor. There is a name column and an active flag column. You can use the active flag column to temporarily or permanently exclude a server from monitoring. The DDL looks like this:
1 2 3 4 | CREATE TABLE [dbo].[ServerList]( [ServerName] [nvarchar](128) NOT NULL, [IsActive] [bit] NOT NULL ) ON [Data] |
The second table stores the captured data. Its DDL looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE [dbo].[DatabaseFileStats]( [ID] [int] IDENTITY(1,1) NOT NULL, [TIME_TAKEN] [smalldatetime] NULL, [DATABASE] [nvarchar](128) NULL, [FILEID] [smallint] NULL, [FILE_SIZE_MB] [decimal](12, 2) NULL, [SPACE_USED_MB] [decimal](12, 2) NULL, [FREE_SPACE_MB] [decimal](12, 2) NULL, [GROWTH_MB] [decimal](12, 2) NULL, [DISK_VOLUME] [nvarchar](2) NULL, [NAME] [sysname] NOT NULL, [FILENAME] [nvarchar](260) NOT NULL, [FILEGROUP] [sysname] NULL, [FG_File_Size_GB] [decimal](38, 2) NULL, [FG_SPACE_USED_GB] [decimal](38, 2) NULL ) |
…note that you can throw a clustered index on the identity column if you want.
The PowerShell is quite straightforward. We grab the list of servers and then iterate thorough that list in an outer loop. For each server in the outer loop, we grab the list of online databases from master.sys.databases and iterate through it. One thing that is missing from that script is a routine to ping each server prior to trying to hit it. I’ll add that as time permits.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #File: GetDatabase File Stats Import-Module SqlServer $Servers = invoke-sqlcmd -ServerInstance $env:computername -Database "DBA" -Query "SELECT ServerName FROM dbo.ServerList where IsActive = 1 order by ServerName" write-host $Servers.ServerName foreach ($Server in $Servers) { $Databases = invoke-sqlcmd -ServerInstance $Server.name -Database "master" -Query "SELECT name FROM sys.databases where (database_id > 4 or database_id = 2) and state_desc = 'online' order by name" foreach ($database in $Databases) { invoke-sqlcmd –ServerInstance $Server.name -Database $database.name -Query "INSERT INTO [dba].[dbo].[DatabaseFileStats] ([TIME_TAKEN]c ,[DATABASE] ,[FILEID] ,[FILE_SIZE_MB] ,[SPACE_USED_MB] ,[FREE_SPACE_MB] ,[GROWTH_MB] ,[DISK_VOLUME] ,[NAME] ,[FILENAME] ,[FILEGROUP] ,[FG_File_Size_GB] ,[FG_SPACE_USED_GB]) SELECT CAST(GETDATE() AS SMALLDATETIME) AS TIME_TAKEN, DB_NAME() AS [DATABASE], a.FILEID, [FILE_SIZE_MB] = CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)), [SPACE_USED_MB] = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) , [GROWTH_MB] = CONVERT(DECIMAL(12,2),ROUND(a.growth/128.000,2)), [DISK_VOLUME] = LEFT(A.FILENAME, 2), a.NAME, a.FILENAME, fg.name AS FILEGROUP, [FG_File_Size_GB] = SUM(CONVERT(DECIMAL(12,2),ROUND(a.size/131072.0,2))) OVER(PARTITION BY fg.name), [FG_SPACE_USED_GB] = SUM(CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/131072.0,2))) OVER(PARTITION BY fg.name) FROM sys.sysfiles a LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = a.groupid ORDER BY fg.name --LEFT(A.FILENAME, 2) , SIZE DESC" -QueryTimeout 3600 } } |
