Red Gate forums :: View topic - SQL Backup fails and causes virtual memory issues
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

SQL Backup fails and causes virtual memory issues

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2, 3
Author Message
howarthcd



Joined: 16 May 2007
Posts: 48

PostPosted: Thu Feb 26, 2009 8:35 pm    Post subject: Reply with quote

Everything seems to be in order there then.

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

Chris
Back to top
View user's profile Send private message
swirl80



Joined: 23 Oct 2008
Posts: 18

PostPosted: Fri Feb 27, 2009 7:18 pm    Post subject: Reply with quote

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:0Cool 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............
Back to top
View user's profile Send private message
howarthcd



Joined: 16 May 2007
Posts: 48

PostPosted: Fri Feb 27, 2009 8:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
swirl80



Joined: 23 Oct 2008
Posts: 18

PostPosted: Thu Mar 05, 2009 1:43 pm    Post subject: Reply with quote

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 Confused

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...?
Back to top
View user's profile Send private message
howarthcd



Joined: 16 May 2007
Posts: 48

PostPosted: Thu Mar 05, 2009 2:06 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
MRATHBUNDBA



Joined: 18 Oct 2010
Posts: 1

PostPosted: Mon Oct 18, 2010 2:34 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2, 3
Page 3 of 3

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group