PowerShell script to monitor SQL Server error log

• gary1

  SSC-Insane

  Points: 21152

  More actions

  August 15, 2013 at 5:18 pm

  #297711

  I'm trying to use the below power shell script to monitor SQL Server error log:

  Write-host "entering Monitor errorlog"

  . C:\DBAScripts\MonitorErrorLog\dbaLib.ps1

  Write-host "entered dbalib"

  $Servers = Import-CSV "C:\DBAScripts\MonitorErrorLog\Servers_Errorlog.txt" -Header Hostname,InstanceName

  write-host $Servers

  ForEach ($sqlhost In $Servers)

  {

  $sqlServiceName = $sqlhost.hostName

  $InstanceName=$sqlhost.instanceName

  }

  #gc Servers_Errorlog.txt | % {

  #$sqlServiceName,$InstanceName = $_ -split "," | convertfrom-stringdata | Select-Object -ExpandProperty Values

  #}

  #[String] $sqlServiceName=""

  #[String] $instanceName=""

  write-host "$sqlServiceName"

  write-host "$instanceName"

  # Get the Database Engine service name of the SQL Server instance $instanceName

  if ($instanceName -eq 'MSSQLSERVER') {

  $sqlServiceName='MSSQLSERVER'

  }

  else {

  $sqlServiceName='MSSQL$' + $instanceName

  }

  # Only if the SQL Server instance is running, the monitoring starts.

  if ((Get-Service $sqlServiceName).Status -eq 'Running' ) {

  $query= "SELECT * FROM ERRORLOG WHERE Severity >= 1"

  $sqlNamespace= "root\Microsoft\SqlServer\ServerEvents\$instanceName"

  $selections= "ComputerName","SQLInstance", "Error","Severity","TextData"

  Notify-WMIEvent $query $sqlNamespace $selections

  }

  Write-host "leaving Monitor errorlog"

  But getting the below error. Please advise

  PS C:\DBAScripts\MonitorErrorLog> .\Monitor-SQLServerErrorLog.ps1

  entering Monitor errorlog

  entered dbalib

  @{Hostname=hostName; InstanceName=instanceName} @{Hostname=sqldba1; InstanceName=ins1} @{Hostname=; InstanceNam

  Get-Service : Cannot find any service with service name 'MSSQL$'.

  At C:\DBAScripts\MonitorErrorLog\Monitor-SQLServerErrorLog.ps1:60 char:17

  + if ((Get-Service <<<< $sqlServiceName).Status -eq 'Running' ) {

  + CategoryInfo : ObjectNotFound: (MSSQL$:String) [Get-Service], ServiceCommandException

  + FullyQualifiedErrorId : NoServiceFoundForGivenName,Microsoft.PowerShell.Commands.GetServiceCommand

  leaving Monitor errorlog

  PS C:\DBAScripts\MonitorErrorLog>

• Database admin(DBA)

  SSCertifiable

  Points: 5373

  More actions

  February 13, 2017 at 10:20 pm

  #1928267

  Hi Gary,
  Hope you are doing well.
  I need the same script to implement in our environment if so can you please provide the PowerShell script  to Automate Monitoring SQL Server Error Logs with Email Alerts which is HTML format adn from server.txt file  .I will appreciate your help with thissituation,Please help …

  Thanks in advance!!

  Best regards,
  Kumar
  

  SQL server DBA