PowerShell and WMI Server Events (SQL Server)–Who is running DBCC commands on my servers ?


This morning, in the early hours of the day, we had some kind of weird happenings in our SAP environment.

The system showed a massive slowdown, and the BASIS asked our help to find the problem. We have seen that several DBCC CHECKDB command, DBCC TRACEON were running at the same time, something like a 30 dbcc checkdbs

We Killed the sessions and after a few minutes they came back again. Well, we needed to know where these commands were coming, any SAP service or not.

From my machine, in the cluster, I just used ONE PowerShell Line

Register-WMIEvent  -ComputerName RemoteServer `

                   -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER' `

                   -Query "select * from AUDIT_DBCC_EVENT" `

                   -Action { Send-MailMessage  -SmtpServer 111.1.11.111 -From 'SQLExecutive@contoso.com.br' -To 'grpDBA@contoso.com.br' `

                                               -Subject 'Auditoria Comandos DBCC Servidor SQLECC' `

                                               -Body "ApplicationName : $($event.SourceEventArgs.NewEvent.ApplicationName) `n

                                                       ServerName : $($event.SourceEventArgs.NewEvent.ServerName)`n 

                                                       Database ID : $($event.SourceEventArgs.NewEvent.Databaseid) 

                                                       Database Name : $($event.SourceEventArgs.NewEvent.DatabaseName ) 

                                                       DBUserName : $($event.SourceEventArgs.NewEvent.DBUserName ) 

                                                       HostName : $($event.SourceEventArgs.NewEvent.HostName) 

                                                       LoginName : $($event.SourceEventArgs.NewEvent.LoginName) 

                                                       NTDomainName : $($event.SourceEventArgs.NewEvent.NTDomainName) 

                                                       NTUserName : $($event.SourceEventArgs.NewEvent.NTUserName) 

                                                       SPID : $($event.SourceEventArgs.NewEvent.spid)`n

                                                       SessionLoginName : $($event.SourceEventArgs.NewEvent.SessionLoginName)`n 

                                                       Start Time : $([Management.ManagementDateTimeConverter]::ToDateTime($event.SourceEventArgs.NewEvent.StartTime))`n 

                                                       TextData : $($event.SourceEventArgs.NewEvent.TextData)"

                                                        

                   }

Just register the event in the AUDIT_DBCC_EVENT Class and be happy.!!!

ha..after that, we need to be notified on dbcc traceon only…, juts change the query to :

"select * from AUDIT_DBCC_EVENT where textdata like '%Traceon%'"

 Simple, Faster and Clean !!!! Classic PowerShell !!!

#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