Checking SQL Error Logs, Event Logs and Stopping Services with Powershell

It was patching time this week at MyWork so I thought I would share some Powershell scripts I use to speed up the process.

I keep these in their own folder and cd to it. Then I can just type the first few letters and tab and Powershell completes it. Nice and easy and time saving

The first thing I do is to stop the SQL services with the StopSQLServices.ps1

Get the server name with Read-Host then I like to see the before and after using

get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }

This uses the Get-service CMDlet to find the services with SQL in the name and display them. Then we pass the running services to an array and use the stop method with a while to check if the services are stopped before displaying the services again. Note this will stop all services with SQL in the name so if for example you are using Redgates SQL Monitor it will stop those services too. If that could be an issue then you may need to alter the where clause. As always test test test before implementing in any live environment.

Once the services are stopped I RDP using the RDP script which again uses Read-host to get a server and then opens up a RDP with a simple Invoke-Command. This means I can stay in Powershell.

Then I patch the server and reboot using the ping script to set up a continuous ping.

If you want to install Windows Updates via Powershell you can use the details here. I like to jump on the box to keep an eye on it.

To check the event log The EventLog.ps1 script is very simple

Get-EventLog  -computername $server -log $log -newest $latest | Out-GridView

Enter the server name and then application or system and it will display the results using out-gridview which will allow you to filter the results as required. I have another version of this script with a message search as well.

You can simply add where {$_.entryType -match “Error”} if you only want the errors or Warning for the warnings. I like to look at it all.

Check the SQL error log with this script which uses the SMO method

$Server = Read-Host "Please Enter the Server" 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server  
$Results = $srv.ReadErrorLog(0) | format-table -Wrap -AutoSize  
$Results

I love these four lines they make it so easy for me to look at the SQL error log whenever I need to. If you want you can pipe to Out-GridView or even to notepad. If I want to check one of the previous error logs I change ReadErrorLog(0) to ReadErrorLog(1) or 2 or 3 etc. I have a daily script which emails me any SQL error log errors and DBCC errors every day so I am aware of any issues before

Then the AutoServices.ps1 to show the state of the auto start services. Strangely you cannot get the Start Type from Get-Service so I use Get-WMIObject. If any have failed to start then I use Get-Service to get the service  and pipe to Start-Service

This is what works for me I hope it is of use to you

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

StopSQLServices.ps1

RDP.ps1

Ping.ps1

SQLErrorLog.ps1

AutoServices.ps1

Advertisements

One thought on “Checking SQL Error Logs, Event Logs and Stopping Services with Powershell

  1. Pingback: 10 Habits of Sucessful Senior DBAs that You Can Use Today • John Sansom

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s