SQL 2005 Memory Issues

[James_Allcock]

Hi all,

Last week I made some changes to our SQL 2005 server to improve performance. One of the main issues was that it had been operating using almost all of the server's available memory. We have SCOM monitoring things and that triggers an alert when it hits 85% memory usage.

Things were looking OK, it was taking memory, then giving it back, up until 6am this morning. This is when our weekly full backup got to the SQL backups. The server has now grabbed almost all available memory and isn't giving it back. I know the day-to-day usage doesn't need all of this memory, it looks very much like something Backup Exec 12.5 has done.

So, is this normal behaviour for BE or is something going wrong somewhere?

[Steve_W_]

Have you tried performing a backup of the Database with SQL to see if you see the same behavior? 

[James_Allcock]

Not yet. The problem is that it's a live system serving databases for most of our main applications. Taking it down, even briefly, requires a lot of planning and persuasion. It isn't something we can do easily.

As I said, SQL has now grabbed almost all of the physical memory and isn't giving it back. So to try using Management Studio to perform the backups to see if it does the same, I'd need to restart SQL to give the memory back to the OS first. Once I can manage that, I'll test backups with Management Studio.

[James_Allcock]

I finally managed to schedule a time to take the server down and have upgraded the memory in the server to 32GB.

After it had settled down today with users connecting to systems on there, the memory use went up to around 10GB. I performed full backups using SQL's bulti-in backup system, backing up all databases that are included in my BE backup. The memory used didn't change noticeably.

I then ran a BE job, taking full backups of all those databases. Memory use has now gone up to 20GB. the jobs completed a while ago but that 10GB hasn't been returned to the OS.

BE is causing SQL to take more memory than it needs, which is making it very difficult for us to estimate what load this server can handle. We've reached a point where we'll have use use the built-in SQL backup and then let BE pick up those files. As we've paid for an SQL agent and our whole backup is built around BE, this isn't ideal.