rusag2


Anyone know of a query that will cycle the SQL service



Re: Restart SQL Service with a query?

anomolous


This is not a definitive answer...But I do not think it is possible. You can stop the service using the xp_cmdshell extended stored procedure and the net stop command, but there would be nothing to start it.






Re: Restart SQL Service with a query?

oj

Here is an old script of mine that utilizes xp_cmdshell and DOS.

Code Snippet

--recycle sqlserver
declare @s sysname, @cmd nvarchar(1000)


set @s=case when serverproperty('instancename') is not null then N'mssql$'+cast(serverproperty('instancename') as nvarchar(15)) else N'MSSQLSERVER' end

set @cmd=N'net stop '+@s+' /y && net start '+@s+' /y'
exec master..xp_cmdshell @cmd







Re: Restart SQL Service with a query?

Manivannan.D.Sekaran

Doing this operation from the T-SQL is bad idea. It will stop the entire service on the server. If it is shared with multiple apps you are troubling others. Always trust your DBA & do these service restart for unexpected & only required scenario.

I am not sure why you want to re-start the service from T-SQL.

But NET STOP && NET START is the trick. You can get the current service name from the @@SERVICENAME global variable.






Re: Restart SQL Service with a query?

oj

@@servicename will only give you the correct value for default instance. If you depend solely on this value you will have to check to make sure it's not "mssqlserver" for named instance (if it's of any use).






Re: Restart SQL Service with a query?

rusag2

thanks all.

very nice oj