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.
skip1000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
Most SQL MVP's will strongly advise against doing an automated restart, but, here is some code that will do it.

Again, it's a pretty risky idea...
Code Snippet--recycle sqlserver
declare @s sysname, @cmd nvarchar(1000)
 
set @s=case when serverproperty('instancename') is not null then N'mssql$'+cast(serverproperty('instancename') as nvarchar(15)) else N'MSSQLSERVER' end
 
set @cmd=N'net stop '+@s+' /y && net start '+@s+' /y'
exec master..xp_cmdshell @cmd

Open in new window

0
 
Chris MangusDatabase AdministratorCommented:
It's pretty risky to automate restarting SQL Server since you never know what is going on in your database at the time.

What is the reason for a scheduled restart?  Perhaps we can look at that and see why it's needed.
0
 
skip1000Author Commented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Chris MangusDatabase AdministratorCommented:
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.
0
 
skip1000Author Commented:
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.
0
 
Chris MangusDatabase AdministratorCommented:
Well, put the code in a job and let 'er rip!
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you cannot get a sql job to start sql server, because once it stopped, the job will be aborted automatically, as the sql agent is also stopped, resp looses connection...

so, you would need a windows scheduled job to do that, simply.
however, I agree with above that you should not need to do that. if you have the tempdb filling up, there is either
* a job that does not properly commit
* a job that does excessive temp table creation during run... if that is the case, stopping+starting the server will not help, except for 1 day, and the same process that filled up the tempdb will run again, and blow up the tempdb.
  -> result, you will make those operations slower, as the tempdb file has to grow for it...

so: solve the original problem, and you solved 2-3 problems at once.
0
 
Chris MangusDatabase AdministratorCommented:
angellll, the code I posted will stop and restart SQL Server...give it a try...
0
 
ShogunWadeCommented:
"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?
0
 
Chris MangusDatabase AdministratorCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.