Collecting Database File Stats with PowerShell

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
    }
}
This entry was posted in Powershell, SQL Server. Bookmark the permalink.