restart SQL 2008 x64 Standard Server every 24 hrs in windows server 2008

I've an issue with SQL2008 Server that gobbles up memory over time and never releases it back to the OS.
I've set memory restrictions on it and many, many other settings over the last few weeks to no avail.
I figure it's some sort of memory leak (since we had a similarly configured SQL2005 and never had this issue before - so the queries/indexes/client do not appear to have any problems).
Regardless, I wanted to restart SQL2008 every 24 hrs automatically (from Task Scheduler, preferably).
Not sure how to go about this.  I can't seem to find any way from the BOL (not really sure what to look for)

Any ideas?
bd9000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Make sure you configure the following set of things:

* AWE, Min and Max Server Memory.
* If you have several instances in your machine, make sure that there is some room for Physical Memory size after allocating the memory for both instances.

Kindly let me know the no. of instances and Physical Memory available in your system so that we can configure better values for that..
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
I think you're better off troubleshooting your errors (not sure that I can help on that).

As for the restart part, create a batch file with the commands

NET STOP MSSQLServer
NET START MSSQLServer

and schedule the batch file to run in task scheduler.

Note: the MSSQLServer is what the service name was in SQL 2000.  It MAY be different in SQL 2008/5.  Go to the Services admin tool and locate the SQL server service.  Open it and look at the service name in the top part of the dialog that appears.  Substitute MSSQLServer for that service name.

0
Darius GhassemCommented:
Here is a hotfix that helped with my problem.

http://www.mskbarticles.com/index.php?kb=974609
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

ProjectChampionCommented:
My advice is that don't mess up with SQL Server memory mangement. It does eat up all the memory, YES, but SQL Server is equipped with very sophisticated and clever memory management modules that always ensure all the available memeory is used optimally to run the server. If this server is only a DB server and doesn't have any other role, you shouldn't worry at all about SQL Server not returning the memory to OS. This is perfectly normal and your worry is unnecessary.
If on the other hand this is a multi-purpose server, then either separate the roles (SQL Server is not a good cohabitant with any other app/service) or give it it's fare share of RAM and leave it on its own. A healthy DB Server doen't need to be restarted regualrly; in fact doing so can cause a lot of troubles.
0
bd9000Author Commented:
OK,
AWE is turned off (everything is 64bit, so I presume it is not necessary?)

The Maximum memory is set to 4GB (the DB is about 400MB - it's the only one on there - a web DB)

The only other app is IIS7 - running the website (which can consume ~700MB of memory max due to extensive caching - not an issue).

There are 8GB total physical memory.  When SQL Server is restarted, the entire system memory is around 1.4GB (including the cache for the website).  After about 3 days, the system is reporting 7.9GB in use, and runs in slow motion (paging).
Restarting SQL Server 2008, resets the memory.
I have no issue with buying more RAM (can hold 256GB of RAM on this system), but I fear the "creep" will simply have the same effect.

The site was Xferred from a 1.6Ghz opteron dual core machine w/W2K3x64 and SQL2005x64 (standard) and run fine w/4GB ram. No creep, no leaks, (ran as fast as this 8-Core Opteron 2.8Ghz box - go figure - not impressed w/SuperMicro).
If there is a "query" issue, why would SQL2K5 handle the "bugs" and not the more advanced SQL2008?
There are no errors being reported in taskmgr - and I can't find any errors from SQLServer either.




0
ProjectChampionCommented:
Ok, assuming that you've got all the latest service packs, update patches, etc. which you should, it sounds like a serious page-fault issue. I’d check the “pagefile.sys” and make sure it’s the right size – usually 1-1.5 times the physical memory. I’d recommend setting it to manual size to stop the system from growing the pagefile too large too quickly. Ideally you’d want to move it to a separate disk, if possible at all.

Check this article http://support.microsoft.com/kb/889654  to find out more about how you can determine the optimal size for your server.
0
bd9000Author Commented:
Pagefile is set to 12GB (the "recommended" setting) -
I created another one on another harddrive as well (I'll have to reboot the server at 5am, so it will be sometime before I see any results).  

I wish I had granular control of the way SQL Server handles memory (or force it to act like SQL2005 - that worked fine, no memory hogging, no system-wide slowdowns).  This is a VERY common complaint for SQL2008 (as per Google)
0
Darius GhassemCommented:
Did you take a look at the hotfix?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> This is a VERY common complaint for SQL2008 (as per Google)

Make sure that you have applied SP1 for SQL Server 2008 and SP2 for your Windows Server 2008 too..
If that didn't helped, then try applying Hotfix mentioned by dariusq
0
bd9000Author Commented:
I'll have to get to the hotfix again (I spaced it yesterday - need to do this at 5am so I can reboot the server w/o messing up the site)
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
sure.. Check it out and revert so that we can try accordingly.
0
bd9000Author Commented:
ok, put in the hotfix.  Still, the memory grows.  
0
bd9000Author Commented:
oops! thought SP1 for SQL2008 was installed - it was not.  Will give that a try.
0
bd9000Author Commented:
no - it was installed after all.  Oh well. Restarting seems to be the only way to go.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Use perfmon and try to monitor your server performance completely so that you can identify any resource related issues or not..
0
bd9000Author Commented:
hi rrjegan17,  Is there a particular thing I should be monitoring specific to SQL 2008?  
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, monitor counters specific to Memory, CPU, Network, SQL General statistics SQL Memory for nearly 3 days or your complete cycle required for restarting your Server and outputs captured might help us nail down the issue..
0
ProjectChampionCommented:
db9000, back to your original question the following command lines stop and restart the SQL Service on a specific server:
sc \\SERVERNAME-or-IP stop "MSSQLSERVER"
sc \\SERVERNAME-or-IP start "MSSQLSERVER"

for instance:
sc \\SQLPARIS stop "MSSQLSERVER"

The whole thing can be scheduled by putting that command in a "Operating system (CMDExec)" step in your job.
You can also find out the status of the service by the following, in order to set up an alert so you can take the necessary action.
sc \\SERVERNAME-or-IP query "MSSQLSERVER"

"MSSQLSERVER" is the name of the default instance. Each naemd instance has a separate service with a different name which you can find out by checking the service explorer.
At least by scheduling the above you can get more time to find root causes of your performance issue - or get more sleep in the morning. ; )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProjectChampionCommented:
Might seem obvious, but I think it's worth mentioning that since SQL Server Agent relies on SQL Server Service, you should either schedule the above mentioned cmd via another instance of SQL Server on your network or more straightforwardly using Windows scheduler.  
0
bd9000Author Commented:
PerfMon is a great tool, but useless in diagnosing SQL2008 - it's a buggy database, period!
0
bd9000Author Commented:
sc \\SRV1 stop "SQLAgent$SQL2008"
 - see if it stopped, if so continue
sc \\SRV1 stop "MSSQL$SQL2008"
 - see if it stopped, if so continue
sc \\SRV1 start "MSSQL$SQL2008"
 - see if it started, if so continue
sc \\SRV1 start "SQLAgent$SQL2008"
 - see if started (within  a period of time), if not try again 3 x, then send an email alert

now just need to figure out how to get the batch file to check the service status before executing the next line -
ugh! maybe this would be easier in a program than a batch file (batch is, so, 80's)
0
ProjectChampionCommented:
SC query will display if a service is running, e.g.:
sc \\SRV1 query "SQLAgent$SQL2008"
You can run it as above once, just to see what the output looks like. However in order to use it in a batch file, it'd be more efficient to pipe into FIND or FindStr filter e.g.
  C:\> SC \\SRV1 query "SQLAgent$SQL2008" | FIND "STATE" | FIND "STOPPED"
Or
  C:\> SC \\SRV1 query "SQLAgent$SQL2008" | FIND "STATE" | FIND "RUNNING"
The statements above will return an %ERRORLEVEL% = 1 if the text is not found, i.e.:
IF errorlevel 1 GOTO :my_subroutine

Please note the Find filter is case sensitive and also b aware if extra spaces.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.