And … What about SQL Server Profiler Trace Files and Powershell ?


 

windowsushi

Some time ago, I posted about working with SQL Server Profiler Trace files in Powershell.

Read, Filter,Save to SQL Table or XML…Sushi..Anyway FUN with SQL Server Profiler Trace Files in POWERSHELL

I made some changes to the module and I am now posting.

The first change I did was remove the parameters to insert into a SQL Server table by Get-SQLProfiler. I actually just see if it is useful to insert after applying filters or whatever need  in one commom table. If it is to save in different tables for each file .TRC , use the TSQL function fn_trace_gettable.
So I create another function , Save-InfoToSQLTable, to do this. Now, you can filter , sort do it what you want and insert into a SQL Server table before.
 
Let’s see some examples ?

 

Read All Traces on "c:\Profiler\Compras\” and filter for “dbo.fc_pco_round_000” in TextData

dir "c:\Profiler\Compras\*.trc" | Get-Sqlprofiler | Where-Object { $_.textdata -like ‘*dbo.fc_pco_round_000*’ } 
 
Creating the table and saving into SQL Server table (Table Name Created by Function)
dir "c:\Profiler\BlockedProcess_210510_1.trc" | Get-Sqlprofiler | Where-Object { $_.textdata -like ‘*dbo.fc_pco_round_000*’  and $_.reads –ge 100000 and $_.spid -eq 90}  | Save-InfoToSQLTable -ServerName Vader -DatabaseName Testes –NewTable
 
Creating the table and saving into SQL Server table (Table Name by Parameter)
dir "c:\Profiler\BlockedProcess_210510_1.trc" | Get-Sqlprofiler |  Save-InfoToSQLTable -ServerName Vader -DatabaseName Testes  -newtable -TableName TestProfiler
 
Saving into SQL Server table already Created
Get-Sqlprofiler -TraceFileName "c:\Profiler\compras\prf-compras_1.trc" | Save-InfoToSQLTable -ServerName Vader -DatabaseName Testes -TableName Teste

 

Saving into SQL Server table already Created with UserName and Password
Get-Sqlprofiler -TraceFileName "c:\Profiler\compras\prf-compras_1.trc" | Save-InfoToSQLTable -ServerName Vader -DatabaseName Testes -TableName Teste –Username UserName –PassWord YourPassword

 

Well now let some considerations.

1 – Just works in PowerShell X86.

2 – If you’re use large files, use T-SQL  and function fn_trace_gettable. In my tests the T-SQL was superior in upload speed.

I see the feasibility of using this module if you have small files (rollout) and want to filter by various conditions, such as example 2. After that,  save the data already filtered.

You can donwload the module here

POWERSHELL ROCKS !!!!

 

“I can’t stop
the way I feel
Things you do
don’t seem real
Tell you what I got in mind
’cause we’re runnin’ out of time
Won’t you ever set me free?
This waitin’ ’rounds killin’ me

 

She drives me crazy
like no one else
She drives me crazy
and I can’t help myself”

She Drives Me Crazy

Fine Young Cannibals

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 Algo que Esqueci de Categorizar. 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