SQL Backup fails and causes virtual memory issues

2»

Comments

  • 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............
  • howarthcdhowarthcd Posts: 69 Bronze 3
    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
  • 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...?
  • howarthcdhowarthcd Posts: 69 Bronze 3
    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
  • 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
Sign In or Register to comment.