SQL Backup fails and causes virtual memory issues

• 
  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 February 27, 2009 13:18

  - 0 +
• 
  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.
  
  @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 February 27, 2009 14:58

  - 0 +
• 
  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 March 05, 2009 07:43

  - 0 +
• 
  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 March 05, 2009 08:06

  - 0 +
• 
  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 October 18, 2010 09:34

  - 0 +

Add comment

Please sign in to leave a comment.