PowerShell and SQL Server Events–Monitoring DataFile Grow

In this case we will use the Data File Auto Grow Event Class

I created a table to store the data :

CREATE TABLE [dbo].[FileAutoGrow](

    [ApplicationName] [nvarchar](200) NULL,

    [ClientProcessID] [int] NULL,

    [DatabaseID] [int] NULL,

    [DatabaseName] [nvarchar](100) NULL,

    [Duration] [bigint] NULL,

    [EndTime] [datetime] NULL,

    [Filename] [nvarchar](max) NULL,

    [HostName] [nvarchar](200) NULL,

    [IntegerData] [int] NULL,

    [IsSystem] [int] NULL,

    [LoginName] [nvarchar](200) NULL,

    [NTDomainName] [nvarchar](max) NULL,

    [ServerName] [nvarchar](200) NULL,

    [SessionLoginName] [nvarchar](200) NULL,

    [SPID] [int] NULL,

    [StartTime] [datetime] NULL

) ON [PRIMARY]

Then, a simple function to get the data from the $event variable

function Get-DataFileAutoGrowProperties { param ($myevent)

    $myevent |         select     ApplicationName,

                            ClientProcessID,

                            DatabaseID,

                            DatabaseName,

                            Duration,

                            @{Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.EndTime)};Label="EndTime"},

                            FileName,                        

                            HostName,

                            IntegerData,

                            IsSystem,

                            LoginName,

                            NTDomainName,

                            ServerName,

                            SessionLoginName,

                            SPID,

                            @{Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.StartTime)};Label="StartTime"}

}

 

Now it is just query

#lets create the event

#only to database 16..or I can do to all databases, just remove where databaseid = 16

$query = "select * from  DATA_FILE_AUTO_GROW where databaseid = 16"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo sqlevents ;Get-DataFileAutoGrowProperties $event.SourceEventArgs.NewEvent  | Out-DataTable | write-datatable -ServerInstance Vader -Database FileAutoGrow -TableName FileAutoGrow}

Or filtering by duration :

#we can query only that duration > 3000

#Length of time (in milliseconds) necessary to extend the file.

$query = "select * from  DATA_FILE_AUTO_GROW where databaseid = 16 and duration > 3000"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo sqlevents ;Get-DataFileAutoGrowProperties $event.SourceEventArgs.NewEvent  | Out-DataTable | write-datatable -ServerInstance Vader -Database FileAutoGrow -TableName FileAutoGrow}

In fact you can query any column that is filterable in the Data File Auto Grow Event Class

Watch the Video (expand..this video was not so good)

#PowerShellLifeStyle