November 22, 2005 at 3:59 pm
Does anyone use a job to “Bounce” (stop/start) SQL server? 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.
November 22, 2005 at 4:33 pm
Does your problem seem to resolve after a server reboot?, or just a sql server service stop and start?
In any case it sounds like you have found the shotgun approach that will solve your problem, but I would be reluctant to take it to the next level by automating the stopping and restarting of sql server, (although you can accomplish this thru a scheduled task in windows and use the net stop {ServiceName} and Net Start {ServiceName}).
But you should further concentrate on the reason this is becoming a problem. Does the application call custom extended stored procedures that do not release the memory? Does the application declare xml documents using openxml, and not properly close the document?
Is the size of the tempdb becoming extraordinarily large? (Stop and restart will reset the db to the default size).
Much investigation you must complete.
November 22, 2005 at 5:21 pm
I am filling in as a temp for a DBA who left a few weeks ago until they can hire another under paid direct hire. Today when users cried that thier aplliaction was slow, I got the application support person on the phone and left him drive. This is an old application version that is over due for upgrade. He stopped and restarted sql server service to correct the problem. He recommended doing this until we up grade. It seems to happen every few weeks so a weekly job should do the trick. This place has many more problem than this one such as commom use of SA, raid zero, simply daily full back ups, dusty old hardware, poor a/c and high temperature in the computer room. Bouncing sql services until they upgrade both hardware and application version seems like the thing to do.
November 22, 2005 at 8:25 pm
Dear Michael,
It is impossible to schedule a job in sql server to restart SQL services
U have to create a batch file which will restart ur SQL Services.
u can use net start to stop and start sql services.
See BOL for the help
from
Killer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply