June 12, 2013 at 8:50 am
Hello,
I have had an issue on a SQL and SSRS server that i have been unable to resolve without getting someone to restart the application services to drop and recreate the TCP connections made to SQL. I have used Quest to look into this but can find very little apart from which SPID it is and where it is coming from.
We are using WebSense and on the server there is one TCP connection of concern, that at the start, has a memory allocation of 2....and 10 days since that restart....it is 474,849....which equates to around 3.8GB. We have a MIN and MAX memory setting of 4.8GB.
Currently we have 3MB for the procedure cache when it was originally just under 2GB....and 1GB for the buffer cache where it was originally just under 2GB also. This is not a heavily used server so luckily the performance impact is minor. However, since yesterday the alerts for recompilation (all <40%) are driving me crazy!! The procedure cache is shrunk all the way down....and once the procedure cache is down to its smallest, the buffer cache starts to suffer and lose its allocation.
I refuse to stop the alerts on this server just to make it quiet. I have checked the DBCC MEMORYSTATUS which gives very little information that i have already. The MEMORYCLERKS DMV shows 3.7mil single-pages for SQLCONNECTIONPOOL. ReportingServices is only using 150MB and doesnt seem to be used much anyway.
I have logged a call with WebSense but they dont seem to have any idea what could cause this. I was hoping someone here could point me in the right direction or to assist in trying to get more imformation, and more importantly, a way to show WebSense, or myself, the cause of the issue and hopefully resolve it.
I have read that something using prepared statements can cause a memory leak....but unsure how to find any evidence for this.
The server details are:
Windows 2008 R2 SP1
SQL 2008 SP3 Enterprise (10.0.5500)
2 x vCPU
8GB vRAM
I saw another post relating to a similar issue but no answers. My memory usage graph is a dead straight line with no deviations....meaning the consumption was constant. I would not expect the WebSense logging utility to be as busy during the day as at night so was thinking this was not related to the SQL activity....more along the lines of the type of connection or something in the application....but again.....how do i show this?
Any help would be much appreciated.
Regards,
Samuel
June 17, 2013 at 8:31 am
hmmmm....nothing??
June 17, 2013 at 9:46 am
"3MB for the procedure cache"???
Did you really mean 3 GB for the procedure cache?
How many connections are open to the server?
June 18, 2013 at 4:26 am
there are only 15 connections....rarely more than 2 active at any time
and yes....the procedure cache started at 2GB but was reduced down to 3MB once the connection memory was so high is caused the memory allocation to the cache to be reduced
thanks
June 21, 2013 at 2:32 pm
Did you get the Solution for the same?i am facing the same problem on my server.
Total amount of dynamic memory the server is using for maintaining connections:-7.718276977GB
Its a 16 Gb box with(10GB max memory) lock pages in memory is enabled.
it seems that there is some problem with ODBC connection from Websense Server.
June 21, 2013 at 3:06 pm
I got the answer..this issue is fixed in 2008 R2 Sp1 CU5.
June 24, 2013 at 6:08 am
thanks deepak....
i dont have R2....and cannot find the particular fix detailed in the CU5 release notes.
Do you or anyone know exactly what fix this is and whether it can be downloaded and applied to SQL 2008 (non-R2)??
many thanks
December 18, 2013 at 8:22 am
Were you able to find a resolution to this issue? I am encountering the same issue with Websense 7.7.3 and SQL Server 2008 R2 SP2 w/ CU3. User connections slowly eat up all the memory allocated to the instance hosting the log databases. ARGH! :-S
February 18, 2014 at 11:07 am
I am having this problem as well. I have a SQL Server 2008 R2 SP2 STD on 16 GB memory and Win 2008 R2 64 BIT, at the beginning, the Memory Manager -> Database Memory takes most of the SQL used memory, but the Memory Manager -> Connection Memory counter keeps increasing until it consumes almost all memory after about a week. I have to restart SQL server to release the memory used by Connection Memory.
When I cut the connections by websense accounts, the memory was release right away. So for now I set a job to cut the websense connections each sunday.
February 23, 2014 at 9:53 pm
how to cut the connections by websense accounts, to release the memory right away.
please guide me by some steps
February 24, 2014 at 12:48 pm
Try using this option "Optimize for Adhoc workloads " .. This reduces memory consumption by adhoc query plans and helps in eliminating procedure cache excessive consumption.
On 32 bit systems, Procedure cache is limited in size. 64 bit systems are OK.
--
Best Regards
February 24, 2014 at 4:20 pm
To cut the connection you can loop the connections by the account and kill them or set single mode for the wslogdb70 database and set back multi mode.
Alter database wslogdb70 set SINGLE_USER with rollback immediate
March 18, 2014 at 1:18 pm
Hi,
You need to change the websense setting from ODBC to BCP.Its a known bug in ODBC drivers which keep the connection open from websense log.
August 29, 2014 at 8:54 am
The issue with Websense is caused by a memory leak on the ODBC driver running on the Websense Logserver machine that makes connection to the SQL server. You should run the following query to identify if the leak is actually caused by the Logserver component
select * from sys.sysprocesses
Check the memusage column in this output. Any values higher than 10 indicates a memory leak situation. You can check out if any entries in the memusage column are higher than 10 and then check the Program Name column. If it says "Websense Log Server" then the leak is caused by the SQL Native client
The Websense Logserver component uses SQL Server Native client to connect to your instance of SQL server
When a prepared statement such as sp_prepare is executed, the SQLFreeStmt ODBC API function does not release the handles of the prepared statements
In this scenario, a memory leak may occur in the instance of SQL Server.
Please see the following microsoft KB
http://support.microsoft.com/kb/2422010
In order to fix this issue please upgrade your version of SQL Server Native Client (ODBC Driver) that is running on the Logserver machine. Upgrading the Native Client version to R2 SP2 (CU5) should fix the issue. You can check the current patch level of your Native client at the registry key below
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Native Client\CurrentVersion
Please follow the above microsoft KB for applying necessary fix.
This is an issue caused by the SQL native client, therefore any applications using SQL Server Native client to make a connection to the SQL server will have this issue. Hence the issue is not Websense specific.
Ravi Desai
Technical Lead
Websense UK Ltd
MCTS - SQL Server 2008
April 6, 2015 at 11:21 am
Did you mean "2008R2SP2CU5" or "2008R2SP1CU5". Because apparently this issue was resolved in SP1CU5. Currently our server is on SP1CU9 and we are seeing user connection memory rising slowly over time. Please advice as I have been looking into this for a while and don't know what to do regarding it. Based on some of the posts I have seen some people say it was fixed after installing SP2 while some say it did not get fixed. We are currently on websense version 7.7
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply