SQL 2000 cause memory leakage?

Question

SQL 2000 cause memory leakage?

Vivi

SSC-Addicted

Points: 457
More actions
December 1, 2004 at 10:37 am

#87224

Hello,
Does anybody know why our SQL server can consume more than 1 gb memory? It was 100 mb when the server first started, but keep increasing to more than 1 gb withing 2 days? Is it leaking, or could something else causes it to leak? We have on average 30 some database connections.
Thanks.
Vivi

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

Its not leakage. SQL, by default, will grab memory from the system as it needs it and will keep it unless another process needs it. So, if at some point, SQL needs more than a gb of memory, it will take that from the OS, and hang on to it in case it needs it again. This is one of the reasons that it is best not to place any other applications with SQL Server. You can place a maximum limit on what SQL can take. In Enterprise Manager, right click on the server name, select properties, memory, and adjust the slider control for max memory. If I do this at all, I set the max to allow just enough for the OS, and let SQL have everything else.

Steve

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

How large is your database? How many databases in same SQL Server instance and what are the total size of databases?

What is SQL Server service pack level? It could be memory leak either.

Vivi SSC-Addicted Points: 457 More actions · Answer 3

All our databases, including systems, are in total 13 gb.

We have sp 3a.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

With 13Gb databases, I agreed with Steve's and It doesn't look like memory leak.

Vivi SSC-Addicted Points: 457 More actions · Answer 5

Steve and Allen, thank you so much! I am looking for the reasonable memory size for OS and will assign an upper bound for SQL.

chrisluthy SSC Rookie Points: 34 More actions · Answer 6

SQL by default already leaves ~128mb for the OS. If SQL is the only program running on a given server, you shouldn't mess with the max memory. OTOH, If you're running SQL/IIS/DNS/DomainController all on one box, you should limit SQL so that the other processes aren't negatively affected by SQL taking more than it's fair share of memory.

barsuk SSChampion Points: 12280 More actions · Answer 7

shot in a dark: Can it be open connections to your Server, AFAIR each open connections occupies 24kb. Also check out sp_who2 (sp_who2 active)

John F SSCommitted Points: 1597 More actions · Answer 8

In some cases adding appropriate indexing can decrease the memory growth. You might have some queries that are doing a lot more work, generating temporary tables, or doing full table scans because some indexes are lacking.

Vivi SSC-Addicted Points: 457 More actions · Answer 9

Our memory stabilized at 1GB ava. memory and SQL is using 1.7 GB, which is fine for us. Thanks everybody! I will examine our databases and applications more closely so the system will run more efficiently.