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
Ja CheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
0
Ja CheAuthor Commented:
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!
0
lcohanDatabase AnalystCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you running on a 32bit or 64bit system?
The question if for Windows and SQL Server.
0
Ja CheAuthor Commented:
@lcohan I will read those article and see what will work best.

@Vitor Montalvao Server 2003 is x64 and SQL 2005 is x86.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Ja CheAuthor Commented:
@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!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, replace the 32bit version for a 64bit one.
0
Ja CheAuthor Commented:
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!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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)?
0
Ja CheAuthor Commented:
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!
0
Ja CheAuthor Commented:
I enabled the -g startup parameter and allocated 512MB for the VAS in SQL Server. This seems to have fixed the issue. I don't think there was sufficient memory to run the job outside the server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ja CheAuthor Commented:
This enabled the backup jobs to run.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.