August 3, 2010 at 7:05 am
Hello,
Today I realised I have to schedule an application that will do stop-start for the sql server service. The only problem is that my Sql Server 2005 is installed and configured on a cluster environment and I'm sure that when doing stop, then the other node will start the service and I don't wanna do that.
Can you please help me with a suggestion?
How can I configure or what should I do in order to make an automatic way to restart the sql server service on the same node?
I want to do this restart automatically (scheduled), weekly, in order to clean the tempdb database.
Thanks a lot,
Wish you good ideas! 🙂
Andreea
August 3, 2010 at 7:52 am
Hi again,
I've just found something...
The script should be something like:
cluster /cluster:cluster-name group "group name"/offline
cluster /cluster:cluster-name group "group name"/online
That's to be done in order to "restart" sqlserver service on the same node.
Wish you good ideas! 🙂
Andreea
August 3, 2010 at 3:38 pm
What I want to know is: Why do you need to "clean TempDB?" TempDB is just that, temporary! Objects created there disappear when their creating SPID disconnects. If this isn't happening you should get the app fixed. It's definitely not recommended best practice to restart your SQL Server Service every week.
I would assume the requirement to do this came from the vendor, but I think it shows a lack of understanding of how SQL works and a certain level of poor programming.
I'm Interested in why the vendor has asked for this.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 3, 2010 at 4:13 pm
I agree with Leo - there is no reason for you to have to implement something like this. If there is a problem with tempdb - then you need to fix the application code causing the problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2010 at 1:48 am
Hello again,
Thank you very much for your responses. It's obvious that's not a solution and thank you very much for pointing that.
The reason for this tempDB "clean" is because its size (data file) increases undefintely... and the .mdf file reserved space on the disk will increase too much (it has now 10G in just one month).
In the same time, I knew it's good to do a restart from time to time. Knowing that tempdb will empty and decrease at the restart, I thought a scheduled restart will make a great job.
I left the "restart" idea behind and I'll check other things and settings.
Thanks a lot!
Wish you good ideas! 🙂
Andreea
August 4, 2010 at 3:17 am
Andreea
I would like to drow your attention that when you restart the server tempdb will shrink and when the space is required it has to expand. Now expanding the tempdb frequently is not a solution. Tempdb is 10G beacause it require that much space.
I guess you need to find out the root cause what's make tempdb to grow? Usually your database maintenance plan do so. But I feel 10G is all right for tempdb.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
August 4, 2010 at 3:39 am
Rather you should focus towards the TEmpdb maintenance.thats the way to maintain tempdb size.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 4, 2010 at 5:24 am
blue_inelush (8/3/2010)
an application that will do stop-start for the sql server service. The only problem is that my Sql Server 2005 is installed and configured on a cluster environment and I'm sure that when doing stop, then the other node will start the service and I don't wanna do that.
For future reference, stopping and starting the sql server service will not cause it to fail over to the passive node!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2010 at 6:26 am
can we opt here MANUAL FAILOVER option ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 4, 2010 at 7:09 am
Thank you all very much for your helpful answers.
Wish you good ideas! 🙂
Andreea
August 4, 2010 at 9:12 am
you're wlecome.
you would need to either move the group via cluster administrator or pull the public network connection on the active node for the service to fail over!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2010 at 5:53 am
No need to failover to restart the SQL service. From Cluster Administrator, select SQL Server resource, then "take offline", followed by "bring online". Remember to "bring online" for SQL Agent as well.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
August 5, 2010 at 7:53 am
Bringing the SQL Server agent online first will automatically bring SQL Server up due to the dependencies between these resources 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2010 at 8:04 am
Perry Whittle (8/5/2010)
Bringing the SQL Server agent online first will automatically bring SQL Server up due to the dependencies between these resources 😉
Not on any of my clusters (I have twelve).
Taking SQL Server offline will also take Agent offline, but manually bringing either online does nothing for the other.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
August 6, 2010 at 2:19 am
Mike Hinds (8/5/2010)
but manually bringing either online does nothing for the other.
then you do not have your dependencies set correctly!
see my attached screenshot for details
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply