Error when trying to compress the backup

Question

Error when trying to compress the backup

Barcelona10

SSCertifiable

Points: 5358
More actions
July 9, 2013 at 12:16 pm

#280117

Hi all,
I am getting error when trying to backup with compression:
'There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server, Error: 701)'.
No problem if i backup without compression.
SQL server 2008 R2 SP1,STD Edition,
total_physical_memory_kb 4193368
available_physical_memory_kb 1042748
total_page_file_kb 8619144
available_page_file_kb 4950160
system_memory_state_desc Available physical memory is high
Any ideas ?
Thank you

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

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

You are using Standard Edition. I don't believe that Compression is available in Standard Edition.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Barcelona10 SSCertifiable Points: 5358 More actions · Answer 2

Barcelona10

SSCertifiable

Points: 5358

July 9, 2013 at 1:20 pm

#1630954

It is available.Trust me

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 3

I think it's what it says. You're memory starved for this operation. Is it a busy instance?

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

Barcelona10 (7/9/2013)
It is available.Trust me

Compression is available, my bad. I did not see that you were on 2008 R2.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Barcelona10 SSCertifiable Points: 5358 More actions · Answer 5

No,it is not busy instance.It started few days .We are backing up prd database (full) twice a day and trn log backup every 2 min.I change server database settings to 'Compress backup' on.Even small trn log backups gives this error .So switched back to 'Compress backup' to unchecked(or off).

Barcelona10 SSCertifiable Points: 5358 More actions · Answer 6

Also these messages were logged in the error log:

"AppDomain 171508 (mssqlsystemresource.sys[runtime].181191) is marked for unload due to memory pressure."

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 7

I suspect you have memory issues.

Check free memory in Task manager, make sure that it isn't too low. In line with that, here's a related thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b43c3631-b28f-4564-a802-7b737966b457/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query

DBCC MEMORYSTATUS will help you debug, and I'd look for anything consuming lots of memory. Note that your SQL memory setting is the buffer pool. There are other memory grants that SQL needs. Debugging this can be complicated, and you would do well to engage someone as a consultant. It might be as simple as adding memory, or reconfiguring. It might mean more complex changes like changing code.

Barcelona10 SSCertifiable Points: 5358 More actions · Answer 8

Thank you Steve.I'll go through your suggestions and i'll post the results. Meanwhile i talked to management and asked them to move prd database to 64 bit box ,so we can add more ram.