Now this may be very simple for some, others might not know what to do about database issues- so Ill explain in a quick post.
I logged in this morning and noticed my RAM usage was very high, (91% on the resource monitor, compared to normal 71%). I hit Crtl-Shift-Esc and went to processess and my top two memory usage processes were SQL Server. The top one was using 1,540,736 K, with the second around 600 K. That is a lot.
Right click on the top one, and select Go To Service. This one is MICROSOFT##SSEE and the second is SBSMONITORING. I know from past experience that SBSMONITORING can get out of control, but in my opinion 600,000 K is not bad. There is a good post on running a script that will clean up and compact the SBSMONITORING database here (Smallbizserver.net- one of my favorite sites, but you will have to find the post yourself).
In this instance, I do not really care about SBSMONITORING. But the MICROSOFT##SSEE is really high, and I have never seen that before.
Now I am no DBA, but I DO know that limiting a database will affect performance. I also know that if the database was using that much memory, it probably had reason. It could be a memory leak, but I do not think so in this instance, because it is just running all of the default services. Do a Google search on SQL Server memory leaks for more information.
So in this case, I do not want to limit the database. Ill restart it and see what happens. Start>Administrative Tools>Services. Right click and restart Windows Internal Database. Voila! it is now hovering around 158,000 K. Thats a lot better than 10x that. And by only restarting it, I did not limit the database should it NEED much more ram, perhaps when it is synchronizing WSUS or something.
Might as well restart SBSMONITORING as well- yep, that knocked the RAM usage of that one down a few notches, though not as dramatically as the first.
So, I want to do this regularly, but I do not want to remember to restart these manually. They get restarted when the server reboots, but I TRY to minimize those as well. We can write it into a VERY simple batch script.
Open up Notepad. Enter the following:
net stop mssql$sbsmonitoring
net start mssql$sbsmonitoring
net stop mssql$microsoft##ssee
net start mssql$microsoft##ssee
Save the file as a text file on the root of some drive, or if you have a folder for scripts. I keep mine in D:\Scripts\.
I go to the new text file location, and change the .txt to .bat
Now we have a file that when it is run, stops both SQL database services and starts them one at a time. This will not cause system damage, nor damage the databases- as limiting the RAM might have. Let’s give the script a test run to make sure it works:
Navigate to the file location from a command prompt, and run the batch file. If the results look like this, then you are good. Now we need to automate this script. So, we will use scheduled tasks to enable this script to run once a week. Could be twice a week, but I think running this Monday morning will be nice.
Start>Administrative Tools>Task Scheduler.
Right click Task Scheduler Library, and click New Task.
Give the task a name and description. In my case I named is Restart SQL.
Select the radio button to run the task if no one is logged on.
You have the option to run this on any account you wish. If you have an account you use for DBA (or even a power user account), then select this account.
On the Triggers tab, we will select on a schedule. I set it to occur Weekly every Monday at 5AM. I know I have no other processes running at that time such as backups. I also put a random delay of 30 minutes on the task- this is not necessary in most cases.
On the Action tab, we will select New. Leave it on run a program. In the box under settings for Program/Script, we will select the script that we made, restart_sql.bat.
On the Conditions tab, pretty much leave stuff alone. Run if computer is idle for 10 minutes, wait for idle 1 hour. Uncheck stop if the computer ceases to be idle. Uncheck wake the computer to run this task- why would a server be in sleep mode?
On the settings tab, pretty much leave everything alone. It’s all self explanatory if need to change something to suit your needs, then do so.
Now, let the task run it’s course. You can check the task scheduler after Monday to see if the task ran- which it will.
This will keep my database memory usage down without me having to worry about it or by limiting the natural functions of said databases.