February 20, 2012 at 9:17 pm
We would like to automate a way to restart SQL at 5:30AM local time every morning. We have an application that hangs until the SQL service is restarted. Cau any one helpus to do with the scripting engine.
February 20, 2012 at 9:49 pm
My 1st suggestion would be to fix the application.
you can create a windows scheduled task with the 'net stop' and 'net start' commands...
eg: net stop MSSQLSERVER
and
net start MSSQLSERVER
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 20, 2012 at 10:01 pm
jyoti.patel (2/20/2012)
We would like to automate a way to restart SQL at 5:30AM local time every morning. We have an application that hangs until the SQL service is restarted. Cau any one helpus to do with the scripting engine.
What exactly the application is performing with database which causes hung?
February 21, 2012 at 12:31 am
I don't know exactly but its a client request to just automate to restart SQL services at 5:30AM
February 21, 2012 at 12:33 am
You mean that need to make 2 bat files one for stop service and another for start.
and schedule like that stop service and after 5 min start. Right....
February 21, 2012 at 12:45 am
2 batch file is not required.create only one batch file with following command.
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
February 21, 2012 at 1:52 am
Do note that this will cause slow application performance at the start of the day, until SQL has repopulated the data and plan caches. Restarting on a regular basis is usually not recommended and not needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 3:35 am
jyoti.patel (2/20/2012)
We would like to automate a way to restart SQL at 5:30AM local time every morning. We have an application that hangs until the SQL service is restarted. Cau any one helpus to do with the scripting engine.
If you provide some details like what happens to SQL Server, what is the CPU usage at that time, or do you feel some memory related issue & to resolve that you want to restart it every morning, people here may help you better to resolve the issue completely.
February 21, 2012 at 3:37 am
Thks
February 21, 2012 at 3:40 am
thks gila for ur note
February 22, 2012 at 12:14 pm
jyoti.patel (2/20/2012)
We would like to automate a way to restart SQL at 5:30AM local time every morning. We have an application that hangs until the SQL service is restarted. Cau any one helpus to do with the scripting engine.
Rather than rebooting the server, which is problematic and probably overkill, you could just take the database offline, which would terminate any active connections in the process, and then bring the database back online again. This is something that can be scheduled in a job.
-- Take database offline. Wait 60 seconds for transactions to complete.
ALTER DATABASE [Problem_Database] SET OFFLINE WITH ROLLBACK AFTER 60 SECONDS;
-- Take database back online.
ALTER DATABASE [Problem_Database] SET ONLINE;
Also, if you think it's needed, the following will also purge the plan cache and data buffer pool for all objects across the server.
-- Removes all elements from the plan cache
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
-- Removes all clean buffers from the buffer pool.
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
Of course, it would be best to narrow down what specifically is going wrong with your server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply