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............
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.
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.
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.
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
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?
Comments
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............
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
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...?
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
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