SQL Backup fails and causes virtual memory issues

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

Postby howarthcd » Thu Feb 26, 2009 8:35 pm

Everything seems to be in order there then.

Do you use any CLR objects (functions, stored procs or UDTs)?

Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby swirl80 » Fri Feb 27, 2009 7:18 pm

No, don't use CLR's. Tried them once for a generic auditing system and got out of memory errors.

It happend again earlier this week, here are the stats when it happened:

1. (16/02/09 15:23) Free 4096 107937792 1122269 237 265977856

2. (23/02/09 13:30) Free 4096 10682368 169200 285 48222208
3. (24/02/09 09:08) Free 4096 786432 98684 334 32960512
4. (24/02/09 09:45) Free 4096 149946368 1554994 193 300113920

these show the memory when i first took it (1), the day before (2) (was slowly dropping over time), the memory captured whilst 100% CPU (3) and memory after restarting the services (4).

I now believe it to be an issue with VAS. I've defragged the c:\\ and up'd the pagefile to 6GB (there is a second page file on another drive which is set to 16GB). Not sure if this is going to help at all but trying everything i can............
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby howarthcd » Fri Feb 27, 2009 8:58 pm

In my opinion everything you've said so far points to a problem with VAS pressure and not to anything 'external' to SQL Server, such as disk fragmentation, page files etc...

The reason I asked about CLR objects is that it seems that when the CLR initialises it consumes about 120-140MB of VAS, which is consistent with the out of memory error that you experienced when experimenting with CLR objects.

Just to confirm - could you post back the string that is returned when you run 'SELECT @@VERSION'?

If you haven't done so already it might be worth setting up a SQL Agent job to log the results of the 'sqbmemory' extended proc on a regular basis, I would suggest a frequency of one minute. This way you can track the 'total' and 'maximum' 'free' values over time and possibly correlate any drop-off with processes and/or jobs that are running on the server at that time. If you use Reporting Services then you could create a report to display the data against time.

Another thing that can cause VAS problems is heavy use of linked servers - do you use these at all? Other causes can be a large procedure cache, large numbers of SPIDs, larger numbers of cursor operations - it's impossible to quantify 'large' for your server, though, without an understanding of the workload.

Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby swirl80 » Thu Mar 05, 2009 1:43 pm

ok, i may be getting somewhere now......or possibly not!

I have since found out that one of our xml imports fails everynight due to it trying to import the file whilst its still downloading and therefore not running the sp_xml_removedocument.

I've been monitoring the VAS every minute and when this failed on tuesday it had zapped the largest available block and not released it. So, i monitored it last night and nothing, no reduction in the total or the MAX available........and it actually failed twice last night :?

Could this just be coincidence? I'm going to continue to monitor it over the next few days and we'll put some error handling in place to fix. I don't want to put the fix in just yet as i want to see if it breaks everything again.

thoughts...?
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby howarthcd » Thu Mar 05, 2009 2:06 pm

I guess that this could be causing the problem.

You could use the following code to close all open XML documents - but note that you should first disable all processes that rely on open XML documents. You should use this at your own risk.


EXEC master.dbo.sqbmemory
GO

DECLARE @hdocument int
--Open a dummy document to get the next handle.
EXEC dbo.sp_xml_preparedocument @hdocument = @hdocument output

DECLARE @i INT
SET @i = @hdocument
WHILE @i >= 1
BEGIN
EXEC dbo.sp_xml_removedocument @i
--The handle always appears to be an odd-number on my test machine
--but you might want to experiment to see if this is true in your environment.
SET @i = @i - 2
END
GO

EXEC master.dbo.sqbmemory
GO


Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby MRATHBUNDBA » Mon Oct 18, 2010 2:34 pm

I'm encountering the same problem. I inherited a CLR process that is leveraging MatLab for some calculations..

BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_281B3B5C-C81B-44C5-92F1-FDE91F76A172'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).

I am running SQLBackup6. The transaction log backups eventually are successful but is there anything that I can do to relieve the memory pressure on a maintenance plan of some sort?

Thanks
Michael
MRATHBUNDBA
 
Posts: 1
Joined: Mon Oct 18, 2010 2:31 pm

Previous

Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests