Sql – automatically restart sql server after job completes

sqlsql-server-2008

I'm looking for a way to automatically restart the SQL service after an agent job has completed or as the last step of an agent job. It occurs to me that the agent service is dependent on the server service, is this even possible?

Microsoft SQL Server Standard Edition (64-bit) 2008 R2(10.50.1777.0)

A little background:

This would be a temporary fix.

We have a sql server that runs an export job for our accounting software the analytic server. After it runs, the sql server becomes unresponsive, sometimes. I am assuming its the export job because with the job disabled no unresponsiveness. This job takes about 30 minutes to complete and is fired off around midnight. Restarting the SQL server service manually fixes the issue and is a temporary work around until we can figure out why the export job is causing problems. I'd like to preemptively restart service after the export job completes before sql becomes unresponsive.

Best Answer

I'm with the commentors; I can't begin to fathom why you'd want to do this. What are you trying to accomplish? Maybe we can help you come up with a better solution.

That said, you won't be able to to do this from within a SQL Agent job (at least fully) since, as you pointed out, the SQL Agent service is dependent on the SQL Server service. Stopping SQL Server stops the SQL Agent. At best you would be able to send a restart signal to the SQL Server service, but you wouldn't be able to get the return status of anything and you lose control over the situation if something were to go south.

You could write a service or a create a scheduled task that monitors the job in question and upon job completion does your restart.

But again, this sounds like a band-aid fix for something.

EDIT: If you have a reliable time after hours when you know the job will have finished then I'd just create a scheduled task to restart the service at that time using a Powershell script or something (restart-service MSSQLSERVER or restart-service -displayname "SQL Server (MSSQLSERVER) assuming you're talking about a default instance.

And then figure out why the service comes to a grinding halt. Run SQL Profiler traces, check performance counters, manually run the things that the job runs to isolate the issue.

Related Solutions

Sql-server – SQL Server 2005 error related to SQL Agent and Database Restores

I seem to have stumbled upon the answer. It would appear that the original SQL server was an upgraded from 7 to 2000 to 2005! crazy but apparently has been in use for 8 years well before my time.

The problem seems to be related to the database compatibility, see the following msoft article

link text

"Databases set to compatibility level 70 or lower are not displayed"

I have tested this by changing the level to 90 and sure enough it now works.

thanks to all who replied, this site is going to be very useful.

SQL Server 2008 R2 backup plan not deleting files older than X days set in maintenance plan

As I understand you delete them in Maintenance plan using special Maintenance Cleanup Task .. or via "Execute T-SQL Statement Task"? Although it should not matter.

By looking at your SQL statement I think it could be the file extension: it should be without dot, just extension on it's own.

I doubt it is a permission issue as you are able to create backups in first place. But check if SQLServerMSSQLUser$... group has full access -- that is how I have on all "backup" locations (these permissions should be set up automatically if you set that folder as default backup destination during initial setup).

AFAIK xp_delete_file is undocumented and I have found no references in SQL Books to it. Apparently SQL Server checks what file it is before deleting it -- it works with own files only (to prevent wrong usage/security, I guess). Here are the format and parameters:

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
N'2011-06-15T15:10:04', -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

By looking at your example SQL statement I see quite a few differences:

In first parameter you have 0 (File Backups) but attempting to delete Text file
File extension should exclude dot
You should provide expiry date (files older than that will be deleted)

Link: http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

Best Answer

Related Solutions

Sql-server – SQL Server 2005 error related to SQL Agent and Database Restores

SQL Server 2008 R2 backup plan not deleting files older than X days set in maintenance plan

Related Topic