PERFCOUNTERS Module Updated – works as background job,bulk insert to SQL, Error Handler and help

Hi all

I made some changes to the PERFCOUNTERS module and they were:

Work as background Job

Now we can work with asynchronous data collection, without locking the Powershell session

A new parameter (RunAsJob) was included in the function, allowing the collection is made in the background.
The job created will be the name of the XML file and the time now (YYYYMMDDHHMMSS)

   1: Set-CollectPerfCounter -DateTimeStart  "05/24/2010 08:00:00" -DateTimeEnd "05/30/2010 22:00:00" -Interval 10 -PathConfigFile c:\testes\testes.xml -PathOutputFile c:\testes\teste.txt -RunAsJob

In this case, a job called testes_20100530091224 (XML Name_yyyyMMddhhmmss)

and to see the Jobs Running :

   1: Get-job -state Running


To get the correspondent Job, use ID or Name (you have this information with the command above)

   1: get-job -name testes20100530091224 | format-list

To see if the job is running without errors [updated] using -keep switch parameter as Marco Shaw explain in comments below :

   1: : get-job -name testes20100530091224 | receive-job -keep

If no error message returns because it’s all  correct

To stop the Job

   1: stop-job -name testes20100530091224
   2: stop-job -id <id job>

Error Handler

If an error occurs, it will generate a log file with the function name corresponding to the error and extension .Log in the folder $home. Find out what your home folder, type $home in some Powershell Session.
Also The error message is displayed on the screen.


Please, type

   1: get-help <function name> -full
   2: get-help Save-PerfCounterSQLTable -full

To see the function help

Inserting TXT into SQL Server Table

For this is not much secret  we can use the TSQL bulk insert.
But Now it has a function that already upload the txt for the table and creating it with the header fields.
You can choose whether to create the table, insert an already created, pass the name to be created or the function generates a name for you.

For use this function, you have to install SQLPSX

Upload the TXT and create a new table (function generate for you)

   1: Save-PerfCounterSQLTable -ServerName Vader -DatabaseName Master  -NewTable  -PathConfigFile c:\Testes\testes.xml -PathOutputFile c:\Testes\teste.txt


Upload the txt into a existing Table

   1: Save-PerfCounterSQLTable -ServerName Vader -DatabaseName Master -TableName PerfCounterSQLTable_20100528100655   -PathConfigFile c:\Testes\testes.xml -PathOutputFile c:\Testes\teste.txt 

Upload the TXT and create a new table with name chosen by you

   1: Save-PerfCounterSQLTable -ServerName Vader -DatabaseName Master -TableName MyTableName -NewTable   -PathConfigFile c:\Testes\testes.xml -PathOutputFile c:\Testes\teste.txt 


At this point, I want to thanks Lauri Kotilainen (twitter|blog) to help me with exceptions in pipeline and job challenge.


I would appreciate usage  feedback , bugs reports, suggestions.. 🙂

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.

9 Responses to PERFCOUNTERS Module Updated – works as background job,bulk insert to SQL, Error Handler and help

  1. Marco says:

    Consider mentioning that when using receive-job that one may want to use the switch parameter -keep, otherwise, any associated output is lost if receive-job is run again.

  2. Laerte says:

    Great Marco, thanks a Lot. Actually I am still learning how to work with Powershell Jobs and comments as yours are perfect. Updated

  3. AprendizSQLServer says:

    O Powershell pode coletar os dados dos contadores do Perfmon e jogar numa tabela, gerar relatórios, etc. Gostaria de saber se poderia filtrar os dados recebidos por uma condição como é feita no alerta pelo próprio PowerShell. Ex: Só coletar os dados de processador se o processamento for > 80%.

    • Ola,

      Não. O módulo que montei não trabalha desta maneira. Por que ? Na verdade seriam dois passos ai da maneira que voce disse. Primeiro coletar os dados para chegar em um baseline. Para isso voce tem que analisar todo o comportamento do percentual de processador, no caso, em varios dias. Essa é a ideia da coleta de dados. Depois de analisado, voce , no caso de % de processador, tira uma média e sabe qual o threshold que teu server trabalha para este contador. Muitos thresholds colocados em cotadores são somente para ter uma base somente. Digamos que teu servidor esta pesado mesmo, e que ele trabalha em 90% de processamento normalmente. Qualquer valor acima deste é anormal. Assim como se ele trabalha a 40% normalmente , acima de 40 é algo anormal.Nao ha a necessidade de esperar chegar a 80%. Entendeu ? Para isso voce tem que coletar varios dias, em picos..e tira uma media para saber qual é o threshold aplicado para o seu servidor, ou criar um baseline como falamos. A partir dai voce pode colocar um alerta para se acima deste valor fazer alguma coisa:)

  4. aprendizsqlserver says:

    Laerte, pedi seu email. Falei com você sobre PowerShell e WMI meses atrás. Seu email é o

Leave a Reply

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

You are commenting using your 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