October 24, 2005 at 6:54 pm
Hi...
The computer where I have SQL Server installed some times does not have enough memory because other programs that use it. But I have found a manner for solution that problem, renitialize the agent and the server, but know I want to do this automatically, does somebody know how I can program a task for do this?
October 24, 2005 at 10:35 pm
The best way to make sure SQL server always has enough memory, is to cap "maximum server memory" and also use "minimum server memory" setting and make sure both values are same.
For example if u have set both of them to 1GB, no matter what happens, once SQL server aquires 1GB it will never let go of that RAM.
The default behaviour is that once OS memory goes below 5 MB, buffer pool is shrunk automatically and SQL server returns ram back to OS.
October 26, 2005 at 7:06 am
I agree with Xearo, however,
'* stops dependent services (Sql Server Agent) , waits 20 seconds and stops
'* MSSQLSERVER
'* this can be set to run as a scheduled task
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colServiceList = objWMIService.ExecQuery("Associators of " _
& "{Win32_Service.Name='MSSQLSERVER'} Where " _
& "AssocClass=Win32_DependentService " & "Role=Antecedent" )
For each objService in colServiceList
objService.StopService()
Next
Wscript.Sleep 20000
Set colServiceList = objWMIService.ExecQuery _
("Select * from Win32_Service where Name='MSSQLSERVER'")
For each objService in colServiceList
errReturn = objService.StopService()
Next
'* then starts MSSQLSERVER, waits 20 seconds, and starts Sql Server Agent
'* run this script after the first
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colServiceList = objWMIService.ExecQuery _
("Select * from Win32_Service WHERE Name='MSSQLSERVER'")
For each objService in colServiceList
errReturn = objService.StartService()
Next
Wscript.Sleep 20000
'* strComputer = "."
'* Set objWMIService = GetObject("winmgmts:" _
'* &"{impersonationLevel=impersonate}!\\"& strComputer & "\root\cimv2")
Set colServiceList = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = 'SQLSERVERAGENT'")
For each objService in colServiceList
errReturn = objService.StartService()
Next
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply