SQL Saturday Chicago #291 – 2014 – I’m speaking

I am very honored to say that I am speaking at SQL Saturday Chicago 2014. A LOT, really a LOT, of  very well known-names in the SQL Server Community will be there and I have the opportunity to be there with them.

My Session will be :

How to properly gather performance counter data using Windows PowerShell

We DBA´s know how important it is to collect performance data. In this session, I will show to you how to properly use the native Get-Counter PowerShell cmdlet to get all information you need from your servers. It has never been as easy to collect performance counter data and save it to a CSV file or a SQL Server table for baselining and later analysis. You may be thinking “But I don’t know nothing about PowerShell and explicitly using .NET classes in my code”. Don’t worry; you don’t need to know this to collect counters. Although this is a 100-level session, you’ll be producing 400-level results and your scripts will scale as the number of servers increase, because we’ll use asynchronous collection and processing. And better yet, we’ll schedule all of it. All this using native PowerShell cmdlets and a minimal amount of script code

Hope to see you there 🙂

Thanks again for the SQLSaturday Chicago Staff.



Posted in Algo que Esqueci de Categorizar | Leave a comment

PowerShell 4.0 PipelineVariable, With or Without, I can´t live…..Without you.

I am writing my new Simple-Talk article of the series The Posh DBA. This one is how to properly use Get-Counter to gathering Performance Counters (windows/SQL/.net..etc) and save to a csv or a SQL Server Table. While writing , I came across an cool example of how to use the new common parameter.

In this article, I am showing that you dont need to know .net classes or even have a intermediate knowldge of PowerShell to write your script to collect the counters. Why ?

Because we are using only the native cmdlets of PowerShell 3.0 and with a script code level 100, I will show to you how to write a process level 400, as collect the data for several servers in asynchronous mode and in background or  even better,  scheduling the entire process using PowerShell 3.0 Scheduled Jobs. I ´ve submitted a session showing all this process to SQLBITS and SQLPASS as well. Fingers crossed

Welll, but this post is about the new PowerShell 4.0 common parameter pipelinevariable. My fellow PowerShell MVP and guru Keith Hill already wrote about it and his EXCELLENT  post is plenty of examples. You should read it before this one, because I am only showing a practical example that I am using.

Keith Hill – PowerShell V4 – PipelineVariable Common Parameter

Generally speaking, this new common parameter allows you to store in a variable  the current pipeline object to use in the further pipeline process. Why that is so F Awesome Amazing Cool ? Just because I dont need to use , as in my example, a Foreach-Object just to create the variable to store the current pipeline object.

Lets see my code without PipelineVariable :

Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' | ForEach-Object { $CounterCategory = $_ $CounterCategory | Select-Object -ExpandProperty Counter | ForEach-Object { $CounterName = $_ $CounterName | Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} | Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"}, @{E={$CounterCategory.Description};N="Description"}, @{E={$CounterName};N="Counter"} } } | Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation

In this example I need to store the first pipeline to $CounterCategory and the second one in $CounterName variables , but I need to use Foreach-Objects , to allow me to use the information for these objects in the further pipeline with Select-Object. If I dont do that I dont have the information of my first  and second pipeline objects 😦

With the New Super UItra Yo-Bro PipelineVariable common parameter that is possible and I dont need to use Foreach-Objects, because the current pipeline object is stored in the variable defined in the pipelinevariable parameter 🙂 :

Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' -PipelineVariable CounterCategory | Select-Object -ExpandProperty Counter -PipelineVariable CounterName | Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} | Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"}, @{E={$CounterCategory.Description};N="Description"}, @{E={$CounterName};N="Counter"} | Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation -Append


With or without you
With or without you
I can’t live

……. without you

Posted in Algo que Esqueci de Categorizar | Leave a comment

PASS PowerShell VC Meeting of March with Kendal Van Dyke


A critical, yet tedious, task DBAs perform on a daily basis is making sure SQL Agent jobs have run properly. Manually checking each instance is slow and doesn’t scale beyond a handful of servers. Agent alerts are helpful but can easily get lost and forgotten in your inbox; Monitoring tools make things a little easier – provided every one of your instances is monitored. What we really just need is a simple and efficient way to see the status of jobs across all instances. Windows PowerShell is a perfect fit for the task, and in this session you’ll learn how to automate building a daily report so that you can see what’s happening with SQL Agent jobs in one place.

Kendal Van Dyke is a practiced IT professional with over a decade of experience in SQL Server development and administration. He is currently a principal consultant with UpSearch SQL, where he helps companies keep their SQL Servers running in high gear. Kendal is a Microsoft MVP for SQL Server, a Microsoft Certified Professional in SQL Server 2008 and 2012, and president of MagicPASS (South Orlando’s PASS Chapter). He also serves as a Regional Mentor for the US Southeast Region of PASS.


Link to Register :


Posted in Algo que Esqueci de Categorizar | Leave a comment

Some reasons to Darth Vader being a badass….

1 – Interview with the new System Administrator :

What? what you mean “I prefer next, next, finish”


2 – After a nice vacation at Korriban (homeworld of Sith), Vader returns to Death Star and get the reports of the SQL Server servers :

Emperor :

“Vader, our new DBA wrote a cool backup process..humm..mmmm..on each server”


3 – No Comments

“I agree, you never will spend, again, half of the morning checking jobs that failed. Apology accepted”


4 – Checking the time frame of the Death Star employees

“WTF ? Is it true that you spent 1 hour manually repeating the same process on each server ?”



Dont be a “next, next, finish IT Professional”. Use the Force, or as it is known in the Galaxy, PowerShell 🙂

Posted in Algo que Esqueci de Categorizar | Leave a comment

Being notified when a child background job is done

PowerShell is so cool that you can be notified when a background job is done. I will not show how to do that, because you can find an excellent explanation about that in this blog post from my good friend Boe Prox .

PowerShell and Events: Object Events

My point is , have you ever tried to use –asjob on Invoke-command ? It will run the scriptblock in a background job. cool !!! But, if you pass more than one server to run this process, lets say 4 servers,  it will create to you one job “Father” and 3 others child jobs. That father  job will  control and report the success of failure of the entire process (all the child jobs)

In PowerShell 2.0  you need to expand the property childjobs to know them. To know what jobs are child  I will create a simples process to sleep the job in a random count. I am using more than one server (in my case the same server more than one time)

If we use the PowerShell 2.0 and get the object $job we will see only one job. The father one :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader'

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id

The result will be :


As we create the job for 3 servers, to check the other ones we need to expand the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs



Now imagine that you are dealing with a lot of long-running process in background jobs in different servers. How can I  check the child jobs status ?(to check how it is going the process in each server)  ?

PowerShell 3.0,  2 new parameters was added. They are -IncludeChildJob –ChildJobState. Lets take a look in the –IncludeChildJob

If I run the same command and now get the job but with –IncludeChildJob  parameter :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob


Cool !! Backing to our example, I have a lot of long-running process in background jobs in different servers. Now I want to know when they finished. I can use the example that I mentioned in the beginning of this post, but it will notify you only when all the child jobs finished because the event is subscribe to the father job. I want to be notifed for each child job in the process. Let´s say that I want to know when he process in each server finished.  The parameter –ChildJobState only will show for me the jobs with the specified state. I will not be notified.

Well, the idea is the same to the father job, I just need to go in each child job and register an event for each one 🙂

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }


if you are using PowerShell 4.0, you can use the new foreach method 🙂

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' (Get-Job $job.id -IncludeChildJob).foreach({ Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } })

Or you can do the same in the PowerShell 2.0, but expanding the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }

Yes..I am using write-host. In this case as I just wanto to show in the screen, I dont need any output piped .

You can change the notification to send email, speak..whatever. It is up to you 🙂

Posted in Algo que Esqueci de Categorizar | 2 Comments

It’s just a matter of respect – at least thank your references.

I am doing this post and to be honest I guess that I dont need to write a blog post about this subject , since in my idea  we are professional and at least we have commom sense.

I am seeing lately an increase in the number of blog posts copied and pasted(that´s Including my local community – PowerShell or SQL Server). Sometimes some words are changed , but the main core you can “fell” that was copied.

I, and I am speaking only for me, I dont mind  if someone copy my content.One way or another it is some kind of respect about me and my work. If someone had the hard work to copy and paste my content, read it , and change the words to not looks like copied, at least some respect for me that person have.

But a LOT of other #sql and #powershell friends does not think like me, and they are completely rigth.  If you have a blog and it is active, lets see some points :

– You need to know about what you are blogging .That´s implies to study a LOT and in many many times miss your time with your family, friends and having fun.

– We are bloggers but also we are workers. Yes..you can think “I see that dumbass all the time in the twitter or facebook. He lives for blogging” Well, sorry to say that, but all the people that I know and are active bloggers are also excellent professionals and work for some company (can be their companys, but are companys). That means that they have to work at least 6/8 hours per day and then, only then , they will study and blogging.

– We do that at least because we love to share and learn. Nobody wants to be better than another. There are spots for everybody and every content.

– We are providing to you free information and in many times we give to you our personal emails and you can contact us directely to share ideas. It means more time after the regular time to our daily job.

– To finish, be a good guy does not mean we are stupid. We know when someone wants to take adavantage of our “good will”, and if we didnt say that it is not because we are stupid nerds. It is because , at least in my case and I am speaking again only for me, I still believe in the soul of the people. When I no longer believe in people, It is because I do not believe in myself anymore. Oh… then actually I have to stop.

Dont be a jerk. Be a good guy. You can Impress other people not only for your knowledge but also for your humility. Thank your references in your posts.

Or at least mention them. I am not speaking for me. As I said, I dont mind. But my friends yes..they mind about that. And as we are a family, #sqlfamily, we care about each other

Again…dont be a jerk.It’s just a matter of respect – at least thank your references.

If you dont have respect for other people, these other people will not have for you. Simple Newton system..action/reaction.


Posted in Algo que Esqueci de Categorizar | 4 Comments

Treinamento de PowerShell 3.0 fundamentals – Março

Pessoal, vamos ter outra turma de PowerShell 3.0 fundamentals aos sabados, iniciando dia 8 de março e indo para os 3 sabados subsequentes, das 8:00 as 12:00 da manhã totalizando 16 horas.

O treinamento é online, via livemeeting.

Quem tiver interesse na ementa, valores etc, me manda um email – laertesqldba@outlook.com


Posted in Algo que Esqueci de Categorizar | Leave a comment