October 14, 2011 at 3:45 pm
Every night when my SQL 2008 R2 does its nightly maintenance task to backup the DB's, it wont release the system memory after it completes.
The Maintenance job is doing:
DB Integrity
Remove Old Backups
Backup DB
Rebuild Index
Shrink DB
Update Statistics
So the next morning the server runs out of resources for SQL or the application to run. I end up have to reboot the server to free up the memory.
Any suggestions?
October 14, 2011 at 10:45 pm
cdumont (10/14/2011)
Every night when my SQL 2008 R2 does its nightly maintenance task to backup the DB's, it wont release the system memory after it completes.The Maintenance job is doing:
DB Integrity
Remove Old Backups
Backup DB
Rebuild Index
Shrink DB
Update Statistics
So the next morning the server runs out of resources for SQL or the application to run. I end up have to reboot the server to free up the memory.
Any suggestions?
"It wont release the system memory..."
"It"?
Which process is not releasing the system memory?
Install the sysinternals suite. In the morning when sql server is having problems, fire up process explorer (procexp.exe). Notice the memory usage of sql server (task manager lies, use procexp.exe)
Key things to look at:
Committed Memory -- how much each program has actually allocated.
Working set -- how much of the committed memory is in physical ram (ie, not paged to disk)
What do the numbers look like for sql server?
what are other large users of memory (of both committed and working set?)
what is the free memory and file cache size?
October 15, 2011 at 1:24 am
cdumont (10/14/2011)
Every night when my SQL 2008 R2 does its nightly maintenance task to backup the DB's, it wont release the system memory after it completes.The Maintenance job is doing:
DB Integrity
Remove Old Backups
Backup DB
Rebuild Index
Shrink DB
Update Statistics
So the next morning the server runs out of resources for SQL or the application to run. I end up have to reboot the server to free up the memory.
Any suggestions?
Monitor SQL Server: Buffer Manager Object with System Monitor.
Checkpoint pages/sec: Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
Lazy Writes/sec: Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Checkpoints & Lazy Writer together flushes the memory. When the Lazy Writer runs, it will only shrink the bufferpool when the OS does not have sufficient memory to service memory requests from other applications. If the OS does not need additional memory, SQL Server does not release any; more committed buffers means faster access and better performance for SQL Server so if it is not required to return memory, it will not.
October 15, 2011 at 6:53 pm
cdumont (10/14/2011)
Every night when my SQL 2008 R2 does its nightly maintenance task to backup the DB's, it wont release the system memory after it completes.The Maintenance job is doing:
DB Integrity
Remove Old Backups
Backup DB
Rebuild Index
Shrink DB
Update Statistics
So the next morning the server runs out of resources for SQL or the application to run. I end up have to reboot the server to free up the memory.
Any suggestions?
First, is that the order you have for your maintenance tasks? If so, I have a few points you should consider:
1) What happens if your backup fails and you already have deleted the previous versions? Can you recover successfully in the event of a disaster?
2) Why both with rebuilding indexes, if you are going to undo all that work with a shrink? You should never schedule a shrink operation - it should only be used after an unusual event caused the database to grow or you have deleted a huge amount of data and you will never use that space in the database again.
As to your question on memory - I am assuming x64 hardware and x64 version of SQL Server. And, I am assuming you are either running as local system, or your service account has been granted lock pages in memory rights, or it is running as local administrator. If that is true, you have to set max memory for SQL Server or it will take all the memory on the server - and it will not release it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2011 at 8:23 am
Sorry when i said "it" i meant the SQLservr.exe service. The SQL Server is configured to use MAX 13GB.
What i have been noticing is that before my nightly maintenance schedule starts the Server will have roughly 13GB free memory and then when the maintenance starts the server goes down to 9GB free memory. The SQLServ.exe is the service that takes the memory and wont release the memory.
Will the maintenance job keep the memory allocated to SQL until the service or server is rebooted?
October 17, 2011 at 8:31 am
Will the maintenance job keep the memory allocated to SQL until the service or server is rebooted?
Will it release memory? Yes
Will it reach to min server memory? It depends... SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.
October 17, 2011 at 8:38 am
so if the sql service doesnt release the memory any time after the maintenance job completes, is there a problem with SQL or is it just going to keep the memory allocated?
October 17, 2011 at 8:50 am
cdumont (10/17/2011)
so if the sql service doesnt release the memory any time after the maintenance job completes, is there a problem with SQL or is it just going to keep the memory allocated?
Posting it back... Don't forget to read underlined text 😉
Monitor SQL Server: Buffer Manager Object with System Monitor.
Checkpoint pages/sec: Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
Lazy Writes/sec: Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Checkpoints & Lazy Writer together flushes the memory. When the Lazy Writer runs, it will only shrink the bufferpool when the OS does not have sufficient memory to service memory requests from other applications. If the OS does not need additional memory, SQL Server does not release any; more committed buffers means faster access and better performance for SQL Server so if it is not required to return memory, it will not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply