SQL 2000 w/Sp4 on windows Server 2008

My company has a large customer base running on SQL 2000 databases. (MSDE and SQL Standard)  These databases for the most part are running in Windows 2000 and 2003 environments.

As of late, our servers have been shipping with Windows 2008 and 2008 R2.
We have been installing SQL 2000 on these servers and it now appears there is a memory leak in the system. (MSDE and SQL Standard)

I have attempted to run a copy of SQL 2008 with the database in compatibility mode. But this appears slower!

A quick restart of the SQL service on the older systems appears to take care of the issues for a few days until the memory is used up again.  

Walnut.png
tmoritaAsked:
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.

andyalderCommented:
Why not use your downgrade rights to install Win 2003 instead.
0
lucius_theCommented:
You can limit the amout of RAM that an SQL server instance will use up.
More info:
http://support.microsoft.com/kb/321363
http://msdn.microsoft.com/en-us/library/ms178067.aspx
0
cyberkiwiCommented:
>>I have attempted to run a copy of SQL 2008 with the database in compatibility mode. But this appears slower!

I could give it a try again.  2008 is normally faster simply because of optimisations added in since 2000.
However, if by "slower" you mean certain queries, then yes 2008 processes RIGHT JOINs differently in a complex sql with many many tables, among other things.

The "leak" you observe is SQL using as much RAM as the system will allow to cache things in memory so that data does not need to be read off the disk all the time.  If you have no other pressing need for the RAM (e.g. dedicated SQL box), then that's actually desirable.  Otherwise lucius has shown you how to limit the amount of RAM used.

SQL doesn't really hog the RAM, if anything else needs it, like you start SSMS on the server, it will release the RAM for use.  The argument goes - if you have spare RAM sitting around, why NOT use it to cache data?
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tmoritaAuthor Commented:
I have limited the amount of memory. it appears that SQL does not like to share.
Disk cost on the server is very low. 100kb/s    I think the harddrives could stand to take a little more work.

Unfortunatly we are using these servers to host a single database, (900 tables or so), and terminal services.  we run about 5 - 15 users in the system via RDP to work with the access frontend and SQL backend.  the SQL database is usualy around 1 gig in size.

I am aware of the MSDE limits, so the customers with more than 10 users have been switched over to SQL 2000 Standard.

 
0
tmoritaAuthor Commented:
Downgrade to 2003 on an already installed server half way across the country is not my idea of a solution at the moment. I much as I like a chalange... somehow.. that one seems a bit beyond what I could manage at the moment.

:)
0
lucius_theCommented:
Have you considered (just for a test) using SQL Server 2005 Express ? It's probably not the best idea in the world but... maybe your MSDE databases would work better in SQL 2005 than in SQL 2008. It's a long shot, but when you're testing it all...

Also, I have observed that MSDE may seem faster, but that's only initially. When you run the first few queries against SQL 2005 (or 2008) it RUNS afterwards. Not to mention that you don't have the programmed slowdown with higher number of concurent execution that you already hit with MSDE.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.