question

arvindchhetri avatar image
arvindchhetri asked

Shut down trigger in sql server 2005

Is there any trigger that can catch events before sql services shutdown for sql 2005?
sql-server-2005triggershutdown
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
jimbobmcgee avatar image
jimbobmcgee answered
Why do you want to do this? You might be able to use Notification Services to catch Windows Event 17148 ("SQL Server is terminating in response to a 'stop' request..."), but what would you do with it? At best you'd be in a race condition to run your piece of T-SQL before the engine that runs your T-SQL disappeared. Your better bet might be to use a Scheduled Task in Windows, which triggered on Windows Event 17148 and ran some VBScript, PowerShell etc. But Windows Event Scheduled Task triggers were only available in Windows Server 2008 R2 upwards. If you're running SQL Server 2005, it stands to reason that you *probably* aren't at that Windows version, yet. If it is just an alert or automatic restart you are after, instead, maybe look at the Recovery tab on the Properties dialog of the SQL Server service in Services.msc. This can auto-restart and can even run a program when it stops. Of course, this only runs when the service stops abnormally, and not when it is stopped manually. Which leaves you with scripting a WMI Event to notify on the deletion of a process called `sqlservr.exe` -- specifically binding to an `__InstanceDeletionEvent`. See: - http://blogs.technet.com/b/heyscriptingguy/archive/2010/12/06/learn-how-to-use-vbscript-to-create-permanent-wmi-events.aspx - http://msdn.microsoft.com/en-us/library/windows/desktop/aa394650%28v=vs.85%29.aspx - http://technet.microsoft.com/en-us/library/ff730927.aspx ...for some details (`SELECT * FROM __InstanceDeletionEvent WITHIN 10 WHERE TargetInstance ISA 'Win32_Process' AND TargetInstance.ExecutablePath = 'C:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\Binn\\sqlservr.exe'` might get you close).
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.