Hi, folks. I've a production SQL machine with more than 20 users making transactions 24 hrs in 6 days a week. I've only Sunday for maintenance. The server has fixed 2 GB RAM allocation for SQL. Is it good to Restart SQL ( or machine) to clear the Buffer-Cache( or is it good to keep the cache) ....
The machine needs to be rebooted occaisionally for other reasons, so I wouldn't worry about that. If you start to see free memory problems in your Perfmon traces I'd schedule a reboot, but I'd only expect that after a year or so without a reboot unless you have significant use of the machine from the keyboard (applications can do funny things to services over time).
This is a case of do as I say, not as I do for other reasons. Our infrastructure team makes significant (often daily) use of our servers from the keyboad because that's how things were always done with NetWare, so we've chosen to reboot our servers weekly. We've got other SQL servers outside the datacenter that haven't been rebooted for 16 months, but aren't showing any signs of distress!
Thanx so much for the advice, blindman & Pat.
I scarcely use the SQL machine directly, rather connect through EM or QA over the network. However i've OUTLOOK2000 window opened on the server machine locally all the time for e-mails.
Some times the processor usage counter constantly stays above 60%, i use to close the OUTLOOK and reopen it; gets normal.