SQL Server Performance Forum – Threads Archive
Start & Stop SQL-Server
To prevent a process left open on the server, I received a suggestion to set an automated stop and start the SQL-Server everyday at 3:00AM. Is it a good idea? What are the positive and negative results? CanadaDBAgenerally not a good idea – this is trying to cure a headache ("orphan" process) by cutting of the head (shutting down the database and making it unavailable to all users). If you are in the crunch for time, _and_ if you database does not do anything doing the night, _and_ if you can guarantee that no user will try to access the database during that time – well, you may consider it .
Otherwise, it may be much better idea to put some monitoring in place to detect some processes, and fix the application bug that causes it. Look into BOL for sysprocesses table- it has most of the information you are looking for..
simas
Thanks for your ideas. The SQL-Server is in use from 6:00AM to 8:00PM. This means after 8pm there shouldn’t be any user connected and there isn’t. No one is allowed to work with the server after 8pm. From 8:15pm to 12:30am there are maintenance processes and backups. All these things are finished at 12:45am. If there is a process running after that, it might be a connection which has been left open, i.e. a user has left her/his computer or turned off her/his computer. I had this two times. We are not allowed to change the application at this time. I suggested to check the processes and if there is any, then kill it or have the server to page the DBA. My manager suggests, to stop and start the SQL server. I am now investigating to find out the strength and weakness of this suggestion. Is there better idea? CanadaDBA
I’m with Simas aproach.
What differences are between stop server or kill open connections?.
If you stop server, and after you have to run maintenance plan, SQL has to get memory again and so on. Futhermore, if your manager want to stop server after full backup is one thing, but before backup is to risk. How about if server dont start for any reason? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
The other question would be : when should we stop servers for "Maintenance" issues like "reboot" the system and get it back fresh? Should we check profiler memory status and its impact on performance continuosly, do it by request or do it periodically?
Does that depend on the Hardware? (I guess so!)
Now, my question is what happens at startup? Does SQL-Server try to allocate Memory and other resources from scratch? What else? CanadaDBA
Ar startup, SQL Server try to allocate memory depending on activity. If no activity SQL don’t allocate memory or allocate minimal. When activity begins SQL get memory step by step, no inmediatially.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Instead of restarting SQL Server services on daily basis you can adopt the scripthttp://www.sqlservercentral.com/scr…ryNm=Maintenance and Management &CategoryID=1 to kill all idle users on the database. As referred its not good practice to schedule a restart of SQL SErver services. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for sharing ideas and suggestion. I really appreciate. I am gathering some advise to support my document that we shouldn’t stop and start our SQL-Server to prevent open left processes. Is "Stopping and Starting" to SQL-Server, something like "reboot" to windows? I mean does it refresh the SQL-Server for work? SO is it beneficial? CanadaDBA
Something like that. Main benefit is, in my oppinion, tempdb reduce size to near original. If you have space problem, that is a benefit.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
You shouldn’t really reboot or stop/start services unless trying to work around a problem. Systems like the OS and SQL are designed to cache things, turning them off and back on means more disk activity and therefore slower initial performance… Cheers
Twan
Farhad ,
Please let me know if there are still items you have questions about – restart of SQL Server is "reboot" of the database engine (cleaning out caches, doing instance recovery (rolling forward any committed and rolling back uncommitted transactions), resetting tempdb to sysaltfiles settings in master database, etc). personally, as someone running production oltp systems , reboot is my absolute last refuge (plus recovery on 300-400 Gb database does take time). If your system is very small and if you are really concerned about the impact sleeping user processes have on your performance, reboot may be fine for you…
simas
]]>