SQL Server Performance Forum – Threads Archive
Services OR Windows rebootIt seems a simple question to others, but try to clearly understand the internals, I post here. Sometime, we need to reboot the MSSQLServer/SQLAgent services, or roboot the window server. So under what conditions, we should reboot the MSSQLServer/SQLAgent OR Windows server.
What is really happening during the reboot process? 1. MSSQLServer 2. SQLAgent 3. Windows server
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by xiebo2010cx</i><br /><br /><br />What is really happening during the reboot process?<br /><br />1. MSSQLServer<br /><br />2. SQLAgent<br /><br />3. Windows server<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi,<br />What i have found is:<br />when we restart the sql server it creates new tempdb and releases all the memory(i.e. clearing cache and buffer) and starts acquiring new references.<br /><br />SQLAgent stops all the jobs it was running previously and waits to start the jobs on their sceduled time[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Also would like to know from other guys, all other major activities being done by restarting of services[<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />]
Thanks, Ranjit, Waiting for other input. ——————
Hi, If you have changed the startup account of MSSQLServer and SqlServerAgent Services, then you should restart these services to affect the changes u have done. When u restart MSSQLService it disconnect all users connected to the server and it roll back all uncommitted transaction to bring the db(s) to a consistant state. If u stopped the MSSQLServer Service to restart, it will automatically stop all depended services like SQLServerAgent/MSDTC etc. So you have to restart all depending services also one by one. After you start the SQLServerAgent service, you can use SQL Server#%92s scheduling features. Before stopping you can pause the services which will stop further connection to the server If you restart the Windows server, you may have many instances of sql server on same machine. so all instance will be affected. Madhu
if the problem is caused by SQL or Agent, restart the process,
if the problem is in the OS, then rebooting is required it may require significant skill to determine whether the problem is in the process or the OS, and it is easier to try the process restart to determine whether this clears the problem
IMO if their is a Configuration change in System/OS Level then restart is necessary for Server. When we have problem running a particular process handle by Service we need to restart it and if it won’t up after restart of service…restart server. Generally it you could get idea easily for any error occurance in Service/Server after reading Windows Event Viewer and SQL Error Log most of the time when i get confused about the event id / event description i search for it inhttp://www.eventid.net/. Most of the time in process we set restart services in First/Second Failure and in third Failure Restart Computer so that any emergency can be avoid due to service failure. Regards Hemantgiri S. Goswami
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
What happens to errorlog when SQL service restarted? old logs get archieved and brand new log file created? ——————
Yes, by default that instance server log SQL actions will be logged to a new log by archiving the older logs. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.