skip1000
asked on
How do I automate SQL server restart daily?
Hi,
How can I create a job to have SQL Server restart at a particular point in time daily? Thx.
How can I create a job to have SQL Server restart at a particular point in time daily? Thx.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it is a test environment and the space is limited. the tempdb grows to 22gb after a few days and server runs out of room. i'm not sure which process causes tempdb to grow too big, but i need to keep server available w/ resources which is why i'm looking at auto restarting sql server.
I can see why you'd want to do the restart. You may need to encapsulate this code in some others that will check for running transactions and user connections to make sure you don't restart it while someone is in the middle of something.
ASKER
true. since this a dev server w/ few users, not many ppl will be impacted I think. just need to do this until i can figure out which process is causing the tempdb to grow to 22gb and how quickly it grows to that size.
Well, put the code in a job and let 'er rip!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
angellll, the code I posted will stop and restart SQL Server...give it a try...
"I can see why you'd want to do the restart. " ? If tempdb growth is the problem firstly you should try and identify why as angel says. However in the interim, why not simply schedule a tempdb shrinkrather than a restart?
You can't just run DBCC SHRINKFILE or SHRINKDATABASE on tempdb. If ANY process is using tempdb then the shrink will fail. See: http://support.microsoft.com/kb/307487
Unfortunately, while skip1000 is troubleshooting why tempdb is growing so large he will without a doubt have to restart his SQL Server.
Unfortunately, while skip1000 is troubleshooting why tempdb is growing so large he will without a doubt have to restart his SQL Server.
What is the reason for a scheduled restart? Perhaps we can look at that and see why it's needed.