Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

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.
0
skip1000
Asked:
skip1000
2 Solutions
 
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
 
Chris MangusDatabase 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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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]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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now