Restart SQL Server service on a cluster environment

Question

Post reply

Restart SQL Server service on a cluster environment

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 1

Perry Whittle (8/6/2010)

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

Thanks for the screenshots, Perry.

Now I can see how one could do that. I have always just used SQL installer defaults.

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 2

free_mascot (8/4/2010)

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.

I agree with this statement. Constant shrinking of TempDB is a great way of causing fragmentation in your database as well as causing performance problems when the DB has to grow back up again to take the amount of space it needs for calculating things.

10 GB is not a huge TempDB. I've seen them larger.

If you want TempDB to be smaller, a couple of things to check would be the over use of Temp tables in queries, and Bulk Insert tasks while the database is in FULL Recovery Mode. Changing the recovery mode appropriately or getting rid of unnecessary temp tables in queries will help keep the size down somewhat.

EDIT: To clarify the recovery mode comment, if you're doing a lot of additional processing during a Bulk Insert (formulas, etc.), it will increase the size of the TempDB in addition to the regular TransactionLog file.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

Mike Hinds (8/6/2010)

Perry Whittle (8/6/2010)

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

Thanks for the screenshots, Perry.

Now I can see how one could do that. I have always just used SQL installer defaults.

No problem Mike you're welcome. I always go into cluster administrator post installation to check my dependencies, I'm a trusting soul 🙂

The attached screenshot shows a brief overview of the dependency set for SQL Server resources.

Regards

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

here's a nice article at SSC http://qa.sqlservercentral.com/articles/SQL+Server/70505/

It also mentions these great links on tempdb:

- Working with tempdb: http://technet.microsoft.com/en-us/library/cc966545.aspx

- troubleshooting performance: http://msdn.microsoft.com/en-us/library/cc966540.aspx#EEAA

These links provide good insight on why it is uses and common issues you can have with it.

Best is you read them over ( and once again ) and build your plan of attack to avoid these scheduled restarts.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

Perry Whittle

SSC Guru

Points: 233792

August 6, 2010 at 6:33 am

#1203445

wow this is really going off topic now, the OP only asked about cluster resources failing over from node to node 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 6

Perry Whittle (8/6/2010)

wow this is really going off topic now, the OP only asked about cluster resources failing over from node to node 😀

I'm verry sorry for that :unsure:

but I always try to prevent downtime if that is not really needed :w00t:

Off course you can launch commands for your clustering service ...

ref: "Cluster.exe Commands" http://technet.microsoft.com/en-us/library/cc723245.aspx

or the good old DOS "cluster resource /?" may be a good start.