| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-09-29 : 10:39:18
|
| 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?Canada DBA |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-09-29 : 10:46:44
|
| Wouldn't it be better to fix the application ?If that's not an option, are there users on your server at 3am ? Will they be affected if you stop it ?If so, it's a bad idea... the negative result is cranky users.Otherwise, go for it.Damian |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-29 : 11:37:15
|
| It's a horrible idea. Then you'll have to plan your entire life around the thing stopping at 3:00am. What if you schedule a job for 2:00am that runs an hour and it runs an hour and 15 minutes one day? What if it never starts back up? You should stop/restart SQL Server when you do upgrades only and restart the server only. Any other reasons for stopping/starting it are just bad planning. Redesign gthe process. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-29 : 13:11:44
|
| 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?Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-29 : 15:39:46
|
| Now, my question is what happens at startup? Does SQL-Server try to allocate Memory and other resources from scratch? What else?Canada DBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-29 : 15:44:36
|
| SQL Server does not "allocate" memory at startup unless you set a fixed size. Normally, you want to set a maximum size so it doesn't go above a certain amount. It will then grab memory as needed. The problem is that it doesn't ever release memory, so if you have other applications running on the server, it can reach a point where the server chokes from memory contention. Many times, this looks like a memory leak. It's actually in fact just bad memory management.Do you have any other applications running on the SQL Server? If you do, have you made sure you have enough memory allocated for everything?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-29 : 16:05:59
|
My server is dedicated to SQL-Server. I don't have anything else.So, stop and start doesn't affect on resources that SQL-Server uses, Am I right? And therefore, in my situation, I can do it instaead of locating the "open left" process and kill it. right?Thanks,quote:
Originally posted by derrickleggett
...Do you have any other applications running on the SQL Server? ...MeanOldDBAderrickleggett@hotmail.com
Canada DBA |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-09-29 : 16:21:16
|
| There's some stored procesdures on here somewhere that will kill all active processes. I'd go with that as opposed to stop/starting the server every day, which is (as mentioned) a bad idea. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-30 : 09:04:28
|
| 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? Canada DBA |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-09-30 : 09:09:07
|
| To be honest, if nobody is using the server, there is nothing wrong with restarting it.However, I think it's much better to fix the application that is leaving open connections.Stopping and Starting sql server unloads it completely from memory.Damian |
|
|
|
mjhessler
Starting Member
7 Posts |
Posted - 2005-11-22 : 18:26:15
|
| So if I choose to stop and restart the server using a scheduled job nightly or weekly during the truely off hours, what is the best code to use? What commands are best to use for a job that bounces SQL weekly or even daily? My client's main application slows down in time but all is well after stopping and restarting SQL server. Instead of waiting for the next time they complain about slowness and doing this during the day, I want to create and run a job to bounce the server during off hours. I hope to hear from my more knowledgeable peers with best practices and warnings for this instead of having to learn the hard way. Michael |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-22 : 22:17:13
|
Here's another thing to consider.  Some of the old applications that still use RDO might very well puke if you stop/restart the services. If a constant connection cannot be found, they have a tendency to just die. Since some of that might be code you can't alter also, you could really mess things up by trying the stop/restart approach. You NEED to find out the reason the clients application is slowing down over time and fix the real issue, instead of slapping a piece of baling wire and some snot rags on the top of your SQL Server and hoping the stupid thing runs.You don't have to stop/restart the server for maintenance either. If you have no clients you're worried about after a certain time period, then just start the maintenance operations. They will run. If they don't run, figure out why they aren't running and fix that issue.I don't know who you've been talking to for these ideas. They are BAD ideas!!!!! Tell your boss he's an idiot if you have to. If you find yourself writing custom programs to "kill users" or "restart servers", know 100000% sure that you have gone the wrong direction. /rantMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-11-22 : 22:36:51
|
quote:
Originally posted by crazyjoe
There's some stored procesdures on here somewhere that will kill all active processes.
Here it is:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077I agree with Derrick and everyone else about tracking down the problem with the app instead of making your SQL Server work around it. Either fix the app, or stop using it. "We are not allowed to change the application" is akin to "We must keep driving this car with a leaky gas tank because we're not allowed to fix it at this time". Get your idiot manager to defend that statement before you cave in.Someday one of these workarounds will kill a backup job or a restore job or some other maintenance and you could lose your data...and "because we couldn't change the app" is not what your next employer will want to hear. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-22 : 22:38:48
|
| Or all your companies employees who are now waiting in the unemployment line.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-11-22 : 22:43:37
|
| There is no problem restarting database when it is not used, BUT the execution plans and the buffer cache will be lost. When we start SQL server the first time, it takes time for SQL server to come fine tune itself. If we restart then these execution plans will disappear.RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
|
Start & Stop SQL-Server