Detect a service restart and trigger a script

Question

Hello,

I have developed a script to detect a service stop event by attaching a scheduled task to the eventlog, event ID 7036 and to run a Powershell script upon detecting event id 7036. However, I later realized that event id is not unique to this service and it's written to eventlog by service control manager for every service that is getting started or stopped (not only service stopped event)

For this reason, the script misses execution if the service is quick to get stopped and get started back up again. Also it's getting missed as there are several service restarts by several applications written to the event log at a given time.

Anyone can help me with the logic to detect this kind of a service restart? I am looking to monitor SQL Server service restarts.

Thanks in advance.

Answer 1

I am looking to monitor SQL Server service restarts.

Review / analyse the SQL Server ErrorLog: https://learn.microsoft.com/en-us/sql/tools/configuration-manager/viewing-the-sql-server-error-log?view=sql-server-ver15

Before you invest a lot of time: Even with the information from ErrorLog, you will never get in 100% of cases the cause.

Answer 2

What do you want to monitor?

Simply, if the SQL Server DB engine services have been restarted? So you want to trigger a PowerShell script that fires an event in your monitoring solution after a restart?

Why not use the SQL Server Agent?
Creating a Job/Step which runs only on SQL Server Agent start ...

Agent always stops/starts depending on DB engine... => so job trigger/schedule would be => "Start automatically when SQL Server Agent starts"
https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver15#to-create-and-attach-a-schedule-to-a-job

and then have a step that executes PowerShell
https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-powershell-script-job-step?view=sql-server-ver15

Answer 3

Hi @Arosh Wijepala ,

Welcome to Microsoft Q&A!
Could you try to use the event 17162 in Windows Event.
Click on Filter current log in the right-pan of the event viewer:

Select all event levels, specify the 17162 event ID in the event ID text box and click OK:

Here is my result:

And i did a test that i restarted SQL Server and refreshed it, then a new event 17162 come out.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

The simplest way to accomplish is would be with a startup proc.

Please see:
https://www.mssqltips.com/sqlservertip/1574/automatically-running-stored-procedures-at-sql-server-startup/

Answer 5

Hi there,

-Open the SQL Server Configuration Manager
-Open event viewer -> windows log -> application -> sort the information by date
-Using the SQL Server Configuration Manager Stop the server or perform the events that you need the script to catch.
-With these event ID you can modify the script . You can also use other tools to get the events.

Process Monitor is an advanced monitoring tool for Windows that shows real-time file system, Registry and process/thread activity. You can get the tool from here https://learn.microsoft.com/en-us/sysinternals/downloads/procmon

---------------------------------------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept it as an answer--