SQL2K / W2003 / Memory Swapping during backup

Question

SQL2K / W2003 / Memory Swapping during backup

Robert Vincent

Grasshopper

Points: 13
More actions
May 25, 2004 at 7:52 am

#102032

I've a sql server 2000 std edition on windows 2003 std with 2GB of memory .
The setup of sql server is:
minimum memory: 128 MB
maximum memory: 1500 MB
set working set size=0
We use Netbackup to do the backup of the server.
In day the server functions well. SQL occupies has little close 1500 MB.
When the backup is starting, a process of netbackup needs a lot of memory, about 600 MB during few seconds.
Windows or sql takes the decision to swap the process sqlserver.exe, in order to accord a maximum of physical memory to netbackup.
The system is very slow during the "swapping". And a lot of request to sql server receives timeouts. (Our web site becomes Inaccessible)
And the events are logged in the system log:
Event ID 119 - DISK - delayed non-paging Io requests
The driver for device \Device\Harddisk0\DR0 delayed non-
paging Io requests for 10ms to recover from a low memory
condition.
What can I do to prevent the swapping of sqlserver process ?
I've try to set min memory=max memory=1500 MB and set working set size=1, but nothing has changed.
PS: excuse my bad english, i'm a little french user

Viewing 6 posts - 1 through 5 (of 5 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

It looks as if you have set the correct configuration options, however I would check the run values on these options as they may be different from the config and require a SQL restart.

Shamless self promotion - read my blog http://sirsql.net

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 2

nothing to do with the post ...

but 'i'm a little french user' is the funniest thing i've heard in ages

cheers

dbgeezer

stevevincent SSC Veteran Points: 265 More actions · Answer 3

Monsieur Vincent

We use Netbackup too, but avoid your problem by using a scheduled SQL Backup to disk on the server, and allowing Netbackup to simply do disk backups from there.

I don't know if you will be able to change your backup strategy like this, but if you can I can assure you it works.

Good luck

Steve Vincent

Igor Raytsin Ten Centuries Points: 1341 More actions · Answer 4

You could try to set priority boost = 1, but read in BOL about "priority boost Option" first.

Good luck,

Igor

P.S. Did you reboot your server after setting working set size = 1?

happycat59 One Orange Chip Points: 29389 More actions · Answer 5

The error you are getting seems to be because the disk system you are using is not able to keep up with the number of I/O requests that are being requested.

What is your disk configuration ?