|
Home > Archive > MS SQL Server > December 2006 > Restoring Multiple Databases Drains System Memory
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Restoring Multiple Databases Drains System Memory
|
|
| Aaron Sanders 2006-12-12, 7:12 pm |
| The scenario: I have to restore the databases from 4 applications from a
production site to a remote site. The backups are copied to a directory at
the remote site. I then wrote a script in VBScript that calls Log Parser to
get the list of database backups from the directory they are stored in. It
then loops through the list and uses SQLCMD to restore the databases, one at
a time. The first application has 700 databases, most under 500MB in size,
many under 150MB. It moves through them fairly quickly, restoring them at a
rate of between 10-20 per minute. HOWEVER, it drains the system memory at an
astounding rate while doing so. When the script starts, Task Manager shows
4GB of RAM free out of 6GB total, and 2GB of PF Usage. While the databases
are restoring, the free system memory decreases rapidly and the Page File
Usage inceases rapidly, so that 75% of the way through those first 700
databases, there is only 100MB of free system memory, and the Page File is
5.5GB. At this point the memory utilization levels out and free memory stays
at around 100MB and the PF Usage stays at about 5.5GB. During this time, SQL
Server, SQLCMD, and WScript only marginally increase their memory usage.
After the scipt has finished restoring all of the databases (715 total), I
set all of the variables equal to "nothing" or "empty", and the script ends.
All of the running processes disappear from Task Manager, but the system
memory and PF Usage do not return to their initial values. I checked my code,
and I don't think it is the problem. I configured Perfmon counters, and they
show Page Faults/s in the thousands while the system memory is declining, and
then PF/s levels off at 20-30/s once the memory and PF level off; .10-10 Page
reads/s while the memory is changing, and basically none once the memory
levels off; 0 page writes/s during the entire process. The only thing I've
found so far that restores the memory is to reboot the server. I thought at
first that putting pauses in my script might help, but I haven't tried that
yet since the memory doesn't come back once the script closes.The only thing
I can think of is to try to monitor all running processes with Perfmon to
find out where the memory is going. It sure seems like SQL Server, but I'm
stumpted that nothing shows up in Task Manager. Any suggestions of SQL tools,
known issues, or places to start checking?
| |
|
|
| Aaron Sanders 2006-12-12, 7:12 pm |
| That's a very interesting article. One thing I didn't mention is that the
server is SQL 2005 64-bit, so some of the info on the /3GB switch and memory
limitations doesn't apply. However, that sounds somewhat like the behavior
I'm seeing. the thing that confuses me the most is that the processes don't
show much increased memory usage in Task Manager. While the available system
memory and page file are changing by almost 4GB, the SQL Server process only
increases Mem Usage and VM by about 70MB, according to Task Manager. I just
can't figure out where all the memory is going to. Otherwise, it does seem
reasonable that as it restores each database, it is keeping the pages in
memory, be it RAM or paging. If that's the case and SQL does have all of the
memory, I wonder if there's a way to get it back. I've waited 3 hours before,
and it didn't return. Maybe tomorrow I'll let it go a lot longer and see what
happens. I'm just not comfortable rebooting the server every day.
"Razvan Socol" wrote:
> Hello, Aaron
>
> Read the following article:
> http://sqlnerd.blogspot.com/2006/07...sql-server.html
> to understand if what you are seeing the known, "by design" behaviour.
>
> Razvan
>
>
| |
| Razvan Socol 2006-12-13, 5:18 am |
| Aaron Sanders wrote:
> [...] If that's the case and SQL does have all of the
> memory, I wonder if there's a way to get it back. I've waited 3 hours before,
> and it didn't return. Maybe tomorrow I'll let it go a lot longer and see what
> happens. I'm just not comfortable rebooting the server every day.
SQL Server won't return that memory, no matter how long you wait.
It will return the memory only if some other application asks for it.
To make a test, start mspaint.exe and set Image Attributes to
10000x10000 pixels.
Razvan
|
|
|
|
|