asked on

SQL Database Full Backup Failing to Barracuda 690 Appliance

Hello, I have a nightly full SQL backup job that runs on my Barracuda 690 appliance. It has been failing for a few weeks now. Initially there was an "not enough memory to complete this operation", so I increased the memory from 8GB to 32GB on the VM.

It worked for one evening, then I began receiving the error below:

CRITICAL SQL: STATE - [4] Native error: [18210] Message: [[Microsoft][ODBC
SQL Server Driver][SQL Server]BackupVirtualDeviceSet::SetBufferParms:
Request large buffers failure on backup device 'device6DEC69E9'. Operating
system error 0x8007000e(Not enough storage is available to complete this
operation.).]

I've confirmed both partitions have more than adequate space. I've done some research and attempted to apply a hotfix, but I think it was outdated because we're on SQL 2005 SP4 on Server 2003.

Any input is greatly appreciated!

Thank you,

Jacob

lcohan

Are you using SQL Native backup or some 3-rd party software?
Is the SQL Server MAX memory set to some value in order to leave some available for other processes on that server? If you have 32GB now please set that MAX value to 28GB and try again. It is under SQL Server properties - Memory setting - "Maximum Server Memory (in MB)"

http://saveadba.blogspot.ca/2012/03/request-large-buffers-failure-on-backup.html

Here's some advice from Symantec if you use their backup software and is exactly related to SQL 2005 on Windows 2003

https://support.symantec.com/en_US/article.TECH180899.html

Ja Che

ASKER

Ok, the Maximum Server Memory is currently set to 2147483647 MB. I only modified the VM's physical memory.

Do I still need to decrease it 28GB?

Thanks!

lcohan

Mandatory to do that as SQL Server will use as much as it can therefor causing possible memory starvation and if it operating was OK on 8GB (before you increased it to 32GB) it will me more than OK on 28GB Max RAM.
Actually this is one of the Microsoft recommended setting on SQL servers:

http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

and for VM's please download "Running SQL Server with Hyper-V Dynamic Memory - Best Practices and Considerations" from https://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

You should also consider/look at "Using Lock Pages in Memory Option with SQL Server " http://support.microsoft.com/kb/918483

Vitor Montalvão

Are you running on a 32bit or 64bit system?
The question if for Windows and SQL Server.

Ja Che

ASKER

@lcohan I will read those article and see what will work best.

@Vitor Montalvao Server 2003 is x64 and SQL 2005 is x86.

Vitor Montalvão

Ok, is what I thought. With a 32bit SQL Server you can't access memory higher than 2GB. For that you need to enable AWE (Address Windowing Extensions) and you can do it running the following script:

sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

sp_configure 'awe enabled', 1
RECONFIGURE WITH OVERRIDE
GO

Open in new window

Now you are able to access the rest of the memory and maybe you won't need 32GB at all. The first configuration of 8GB could be enough.

Ja Che

ASKER

@Vitor Montalvao Ok, I'll try that this evening and see what happens. Do you have any other advice in case this does not address my issue?

Thank you!

Vitor Montalvão

Yes, replace the 32bit version for a 64bit one.

Ja Che

ASKER

Hmmmmm.... I did enable AWE last night and the backup failed again.

I don't think replacing the version is a viable solution at the moment. Do you have any other configuration suggestions?

Thank you for the continued assistance!

Vitor Montalvão

Failed with the same error or it's a new one?
Also, what's you memory configuration now (OS, MSSQL min and max server memories)?

Ja Che

ASKER

It failed with the same error.

"Use AWE to allocate memory" is enabled.

Minimum server memory = 8000 MB

Maximum server memory = 28000 MB

As instructed. I'm looking into this article - http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

as a possible solution, but have not enabled the "-g parameter" for SQL startup that will leave available for memory allocations within the SQL server process, but outside the SQL server memory pool.

Also, this is the result of the query when it was ran -

Total avail mem, KB = 42148
Max free size, KB = 4040

I think this is the way to go, but feedback is always appreciated.

Thanks!

ASKER CERTIFIED SOLUTION

Ja Che

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ja Che

ASKER

This enabled the backup jobs to run.