scheduling SQL server to restart
-
Hi All,
I would like ot know that how can i schedule SQL server services to restart every three day Please?
thanks
-
How about scheduling the job restart using Task Scheduler of Windows itself? I am just wondering. May be there is a better solution than this.
Regards
Utsab Chattopadhyay
-
You have to write a batch file using NET START and NET STOP commands..
MohammedU
Microsoft SQL Server MVP -
I don't think there is way to schedule every three days using nt scheduler...
MohammedU
Microsoft SQL Server MVP -
There is using some batch scripting... Keep in mind you can always issue an AT command... that runs under the System account unless the account is specifically configured in Task Scheduler. You could write a script that restarts SQL Server and then reschedules itself.
K. Brian Kelley
@kbriankelley -
Do you mind me posing the question why you would do something like that ???
If it is sqlserver consuming to much memory, you can restrict it if that is what you realy want ?! (sp_configure 'max server memory'..)
By restarting sqlserver, it needs to rebuild its non-persistend structures and statistics, (re)do all the costly I/O operations because it will have to read all the data from disk in stead of from its buffers. All the connections will have to be redone, ...
If you just want to rollover the sqlserver-serverlog-file, you may want to use DBCC errorlog. Schedule it with sqlagent.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I have to restart it due to some billing batch and also web app too .
to schedule it from Windows scheduled task What do i have to do? I mean which file i have to put to start SQL server!!
-
if you are running a default instance of sqlserver :
from books online:
net stop mssqlserver
Stops an instance of SQL Server either remotely or locally if you are running the Microsoft Windows NT® 4.0 or Windows® 2000 operating systems. To stop a named instance of SQL Server 2000, you must enter net stop mssql$instancename from the command prompt.
Start an instance of SQL Server or the SQL Server Agent service from a command prompt by typing:
net start mssqlserver or sqlservr, or net start SQLServerAgent or by running SQLSERVR.EXE. If you are referring to a named instance of SQL Server, you must specify mssql$instancename or SQLAgent$instancename.
Don't forget to start SQLAgent !! if you use sqlserver jobs !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
but how can i schedule that so it can run automatically. Also Agent is dependent on that.
-
Just for Mohammed Uddin. Your statement, "
I don't think there is way to schedule every three days using nt scheduler...",
is too fast. Think about why we cannot?
-
SQLRep:
create a *.cmd file(notepad) that contains these line items:
net stop mssqlserver
net start mssqlserver
net start SQLServerAgent
then go to Control Panel >>Scheduled Tasks for that file; then fiddle with the schedule to your hearts content.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
net stop mssqlserver /Y
Otherwise it'll hang at the prompt to also shut down SQL Server Agent if that is running.
K. Brian Kelley
@kbriankelley -
Sorry wrong posting!
MohammedU
Microsoft SQL Server MVP -
If I am not mistaken we can't schedule the job every three days... there is a option for daily weekly and so on.. but without writing any widow script I don't think we can schedule every three days...Task scheduler is not flexible enough like sql job scheduler...
Here is what I can think...
1. Create a batch file (test.bat) using the using net start command...
net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent
2. Create a sql job using the following code... change the time and day as you needed..
declare @sql sysname
declare @machineName sysname, @Time Varchar(5), @day sysname
select @machineName = CONVERT(Varchar(20), SERVERPROPERTY('MachineName')),
@Time = '10:50' , -- convert(Varchar(5), getdate(),14),
@day = datename(dw,getdate())
select @machineName
select @sql = 'at \\'+@machineName+' /delete /yes'
EXEC MASTER..XP_CMDSHELL @sql
select @sql = 'at \\'+@machineName+' '+@Time+' /every:'+@day+' "c:\test.bat"'
select @sql
EXEC MASTER..XP_CMDSHELL @sql
3. Schedule the job create in step2 every three day or as needed...
MohammedU
Microsoft SQL Server MVP -
Should also note that if applications running against sql server that restarting the services may cause a conflict when restarted.
Example: if an applications is running a transaction through Com + (MSDTC) and SQL Server is shut down this may cause additional problems with the application or SQL Server once restarted. In this cause of course could be resolved by also running the NET STOP MSDTC and NET START MSDTC
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply