May 12, 2009 at 2:29 pm
Hello,
I did some search about this but didn't found what I was looking for. If someone can help, any clue is appreciated.
We have a problem on SQL Server, this server is critical and we can't afford to reboot it that often (every 2 or 3 weeks).
The first symptom is when all linked servers connected to Oracle stop working.
In the SQL Logs, the first error is: "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824"
Then we get a ton of: "Error: 701
We are running SQL Server 2005 SP3 (4035) x86 on Windows Server 2003 R2 sp2 (3790)
Server specs: Intel Xeon 3GHz, 4GB of RAM with a lot of HD space available
Thanks!
Etienne
May 12, 2009 at 2:33 pm
[font="Courier New"]Here is the log:
05/12/2009 10:16:19,spid144,Unknown,There is insufficient system memory to run this query.
05/12/2009 10:16:19,spid144,Unknown,Error: 701 Severity: 17 State: 123.
05/12/2009 10:16:19,spid144,Unknown,There is insufficient system memory to run this query.
05/12/2009 10:16:19,spid144,Unknown,Error: 701 Severity: 17 State: 123.
05/12/2009 10:16:15,spid144,Unknown,There is insufficient system memory to run this query.
05/12/2009 10:16:15,spid144,Unknown,Error: 701 Severity: 17 State: 123.
05/12/2009 10:16:15,spid144,Unknown,MEMORYBROKER_FOR_RESERVE Allocations = 182 Rate = 0 Target Allocations = 164160 Future Allocations = 36551 Last Notification = GROW
05/12/2009 10:16:15,spid144,Unknown,MEMORYBROKER_FOR_STEAL Allocations = 2229 Rate = -85 Target Allocations = 155653 Future Allocations = 0 Last Notification = GROW
05/12/2009 10:16:15,spid144,Unknown,MEMORYBROKER_FOR_CACHE Allocations = 2047 Rate = 309 Target Allocations = 155865 Future Allocations = 0 Last Notification = GROW
05/12/2009 10:16:15,spid144,Unknown,Big Gateway Configured Units = 1 Available Units = 1 Acquires = 0 Waiters = 0 Threshold Factor = 8 Threshold = -1
05/12/2009 10:16:15,spid144,Unknown,Medium Gateway Configured Units = 4 Available Units = 4 Acquires = 0 Waiters = 0 Threshold Factor = 12 Threshold = -1
05/12/2009 10:16:15,spid144,Unknown,Small Gateway Configured Units = 16 Available Units = 16 Acquires = 0 Waiters = 0 Threshold Factor = 250000 Threshold = 250000
05/12/2009 10:16:15,spid144,Unknown,Optimization Queue Overall Memory = 1344798720 Target Memory = 1275109376 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
05/12/2009 10:16:15,spid144,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=7695 Available=7695 Limit=7695
05/12/2009 10:16:15,spid144,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=153900
05/12/2009 10:16:15,spid144,Unknown,Query Memory Objects: Grants=1 Waiting=0 Maximum=146205 Available=146023 Limit=146205
05/12/2009 10:16:15,spid144,Unknown,Global Memory Objects: Resource=307Locks=171SE Schema Mgr=585SQLCache=56Replication=2ServerGlobal=31XPGlobal=2Xact=132SETLS=4DatasetMemObjs=8SubpDescPmos=4SortTables=1027
05/12/2009 10:16:15,spid144,Unknown,Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
05/12/2009 10:16:15,spid144,Unknown,Procedure Cache: TotalProcs=2 TotalPages=44 InUsePages=10
05/12/2009 10:16:15,spid144,Unknown,Buffer Counts: Committed=204800 Target=204800 Hashed=199672Internal Reservation=40960 External Reservation=135Stolen Potential=190199 Min Free=476 Visible=204800 Available Paging File=5838999552
05/12/2009 10:16:15,spid144,Unknown,Buffer Distribution: Stolen=2285 Free=903 Cached=1940Database (clean)=197280 Database (dirty)=2391I/O=0 Latched=1
05/12/2009 10:16:15,spid144,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) VM Reserved = 8192 KB VM Committed = 8192 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 1344 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,OBJECTSTORE_SERVICE_BROKER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 256 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,OBJECTSTORE_SNI_PACKET (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 2904 KBMultiPage Allocator = 48 KB
05/12/2009 10:16:15,spid144,Unknown,OBJECTSTORE_LBSS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 64 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,USERSTORE_SXC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,USERSTORE_OBJPERM (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 3032 KBMultiPage Allocator = 48 KB
05/12/2009 10:16:15,spid144,Unknown,USERSTORE_TOKENPERM (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 1392 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,USERSTORE_DBMETADATA (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 944 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,USERSTORE_SCHEMAMGR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 4656 KBMultiPage Allocator = 32 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_SYSTEMROWSET (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 272 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_EVENTS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERTO (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERREADONLY (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 32 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERRSB (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERDSH (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERKEK (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_BROKERTBLACS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 200 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_STACKFRAMES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 0 KBMultiPage Allocator = 8 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_XMLDBELEMENT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_XMLDBTYPE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_VIEWDEFINITIONS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_NOTIF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_TEMPTABLES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_XPROC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_PHDR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 96 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_SQLCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 200 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,CACHESTORE_OBJCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 72 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 24 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SOSNODE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 5760 KBMultiPage Allocator = 6440 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_HOST (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 184 KBMultiPage Allocator = 64 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLQERESERVATIONS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 1456 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_BHF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 120 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLXP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_FULLTEXT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SNI (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 184 KBMultiPage Allocator = 16 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLHTTP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 136 KBMultiPage Allocator = 192 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLCLR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 8 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 1288 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLSTORENG (Total) VM Reserved = 14720 KB VM Committed = 14720 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 4384 KBMultiPage Allocator = 8512 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLUTILITIES (Total) VM Reserved = 120 KB VM Committed = 120 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 264 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 2464 KBMultiPage Allocator = 72 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLQUERYEXEC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 16 KBMultiPage Allocator = 0 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) VM Reserved = 1654624 KB VM Committed = 1654624 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 0 KBMultiPage Allocator = 400 KB
05/12/2009 10:16:15,spid144,Unknown,MEMORYCLERK_SQLGENERAL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KBSinglePage Allocator = 2920 KBMultiPage Allocator = 2272 KB
05/12/2009 10:16:15,spid144,Unknown,Memory node Id = 0 VM Reserved = 1699544 KB VM Committed = 1697016 KB AWE Allocated = 0 KB SinglePage Allocator = 33424 KBMultiPage Allocator = 18240 KB
05/12/2009 10:16:15,spid144,Unknown,Memory Manager VM Reserved = 1703704 KBVM Committed = 1701024 KB AWE Allocated = 0 KB Reserved Memory = 1024 KB Reserved Memory In Use = 0 KB
05/12/2009 10:16:15,spid144,Unknown,Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824[/font]
May 12, 2009 at 2:38 pm
First check size and utilization of TempDB, lack of TempDB space or lack of free space on the drive TempDB is seating could cause this issue.
Second, you may have bumped into Bug #408980 - please check http://support.microsoft.com/default.aspx/kb/912439
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2009 at 1:56 pm
Hi Paul,
I will try to increase the initial size of tempDB files and see if it helps.
But I'm sure the hd size is not an issue, there is almost 1 To of free space on this drive.
Thanks!
May 14, 2009 at 2:54 pm
Even I got this error couple of weeks back on a server and when contacted MS we are asked to update a patch for memory usage by 2005 server. The cause was said that SQL Server 2005 does not release memory once it acquires but I told them that this is the only server where we had service pack 3 applied.
Any way, the issue was resolved when we moved tempdb to a different drive from the default path. This might not be the perfect solution, but it fixed our issue that time and hope it doesn't come again on that server.
Touch wood.
SQL DBA.
May 14, 2009 at 2:58 pm
Also you can try this.
Try to run the DBCC Checkdb with maxdop 1 so that it doesnot go for parallelism.
And enable the following trace flags to capture error dumps of SQL processes to get 701 error occurs next time.
DBCC TRACEON (8004,-1)
DBCC TRACEON (2551,-1)
SQL DBA.
July 3, 2009 at 9:32 am
Hi everyone,
I just want to let you know that the problem seems to be resolved now... I tried a couple of things but I think what resolved the problem was:
1) Increase the initial TempDb size
2) Change the authentication mode for the service broker. I have read that if the service broker uses the SQL authentication, it causes memory leak over time. The service broker now connects to SQL with the Windows authentication.
I hope this can help anyone else facing this problem.
Thanks to everyone that contributed to this post! 😎
November 18, 2009 at 6:38 am
How do you change the authentication method of the service broker?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply