Solved

How do I automate SQL server restart daily?

Posted on 2007-12-04
10
753 Views
Last Modified: 2008-02-01
Hi,

How can I create a job to have SQL Server restart at a particular point in time daily?  Thx.
0
Comment
Question by:skip1000
10 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20407472
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
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 200 total points
ID: 20407486
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
 

Author Comment

by:skip1000
ID: 20407606
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20407624
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
 

Author Comment

by:skip1000
ID: 20407689
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
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20407711
Well, put the code in a job and let 'er rip!
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 20409184
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
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20411704
angellll, the code I posted will stop and restart SQL Server...give it a try...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 20413854
"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
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20414385
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question