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 Alegre (expand..this video was not so good)

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s