Hello all, I'm currently developing an asp.net site that reads from and writes data to a sql server database. the connections to the database are made with SqlDataSources. I am not manually opening and closing connections in my code, I figured that I could abstract those processes out by sticking with SqlDataSources. I recently ran into the following error while trying to write data to my sql server database on one of my pages:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I read up about connection pooling, and tried restarting restarting the sql server database I'm working on, restarting IIS on my web server, and I've even tried to manually clear the connection pool with SqlConnection.clearAllPools() and SqlConnection.clearPool(my connection string), but I still get the above error.
What else could I try to do short of restarting the server that sql server is on?
I was wondering, if you have multiple SqlDataSources on a single aspx page, does the framework need to open and close a SEPERATE connection for each SqlDataSource. This would make it much less efficient than coding with the ADO.NET objects and just opn and close the connection once irrespective how many quries that page needs. Your comments would be appreciated.
I was watching the "To Do List" AJAX video created by Joe Stagner and while watching the video I noticed (in designer) Joe use a Dataset to populate a Gridview and this got me thinking. I have a few gridviews in my app at work that just use SqlDataSources, and they seem to work fine, but is it better to use Datasets ?
Hi, I'm using the following to send two items in the querystring over to the next page, where I can display tables based on them. Both querystrings appear fine in the URL. The first querystring works fine to single out the order specified by the orderid from the querystring, but I can't seem to get my other sqldatasource to show the userid (+contents of aspnet_users table) using the userid I passed in the querystring. <asp:HyperLinkField DataNavigateUrlFields="OrderID,UserId" DataNavigateUrlFormatString="Copy of Orders.aspx?OrderID={0}&UserId={1}" DataTextField="OrderID, UserId" /> This is what I'm using to try and pick up the userid <asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource3" DataKeyNames="UserId"> <Columns> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="SELECT [UserId], [UserName] FROM [aspnet_Users] WHERE UserId = @UserId"> <SelectParameters> <asp:QueryStringParameter DefaultValue="UserId" Name="UserId" QueryStringField="UserId" Type="object" /> </SelectParameters> </asp:SqlDataSource>
[.NET 2.0, Visual Basic, RadChart/RadPanel/RadTimer controls (www.telerik.com), SQLDataSource controls, SQLServer 2002] I have only been working with .NET for about a month now so my knowledge and understanding are pretty low in some areas, including this one. What I have is a number of webpages that have several RadChart controls on them (8 to 12 charts per page). Each of these charts is bound to a seperate SQLDataSource with a variation of the following query: 1 SELECT [QC Shot Logs].penetration, charges.penmra, charges.penmts, [QC Shot Logs].time2 FROM [QC Shot Logs] INNER JOIN (select top 1 run.runid, run.linerpress#, run.partid3 from run where run.linerpress# = '1' order by run.[date] desc) as drun ON [QC Shot Logs].RunID = dRun.RunID4 INNER JOIN Charges ON dRun.PartID = Charges.PartID5 where [qc shot logs].shottype < 3 order by [qc shot logs].timeEach chart represents the results of a quality control check on explosives that my company uses and puts the data in a visual format so that it is easy to see if the blasts passed or failed inspection. The different variations in the SQL code above are simply a change in line #3: where run.linerpress# = '1', where run.linerpress# = '2', and so on up to '12'. Data is added and modified in the database quite a bit throughout the day at random times, so I need the charts to refresh their data pretty frequently (done with a RadTimer control). The problem that is caused then is that I have 12 RadCharts individually bound to 12 SQLDataSources that all get refreshed every 30 seconds or so, which takes quite a bit of time. The main Quality Control computer monitors four webpages at a time, one of them with 12 Charts/Sources, and three with 8 Charts/Sources each. Having 36 charts up at a time with 36 hits on the database going as well causes a great deal of lag on the system. In addition to that though, there are also two other pages (each with 12 charts/sources) that can be viewed by any number of people at any time, which (obviously) causes more lag on the system. So, on to my actual questions: 1) Can I use a single SQLDataSource control to gather all of the data I need for the 8-12 RadCharts on a single page? I'm not too well versed in SQL either, so I don't know if I can modify the SQL so that it picks up all of the information I need, or if I can gather the data for the first chart, change a variable to pull the data for the second, change the variable again, etc. 2) Is there a way to refresh the RadCharts so that the data they display is relatively real time without having to refresh the SQLDataSources as well? And then, of course, any suggestions at all that you can give me that will help to improve overall performace or anything of the like is definitely appreciated.
Hi, in my web application i had a connection leak now that i solve this problem, i want to check if all connections are really closed. I make a connection to an sql database. I try on the database the sp_who procedue but i think, this is not that i can see if i colse all, connections is there a other way to check or see if all connections are closed.
I have recently encountered what looks like a memory leak within SQL server, but may be a number of other things as well - I'm hoping that some of you may have encountered this problem as well so I may be able to narrow down my list of possible culprits.
I noticed this morning that SQL Server was chewing up about 1.5 GB of memory - obviously way more than it should be. At first, I thought this may be because of a coding error, but I was unable to reproduce the problem on my development machine (running SQL Personal).
After re-starting the SQL service (which cleared up the memory use), I ran task manager on our SQL server machine, and noticed that when queries were executed (especially queries that returned fairly large recordsets), memory consumption used by the sql server process would jump up, but then would not be released (as my development machine was doing).
I'm not a DBA, so I'm hoping this may be happening because of some (unknown to me) option that is not currently enabled/disabled on the SQL Server. My only other thoughts at this time are that maybe it's a bad install of SQL, or perhaps bad RAM? Any supporting/disproving thoughts? Any similar situations?
We are running 2 SQL 7.0 servers which have IIS 4 also installed. The servers are mirror images of each other and are configure the same(1 is test, the other is a production server) I have noticed that even if no one is on the production server the memory being used will peak at almost 100% of the physical memory and peg the CPU at 35-40%. If I shut down the SQL server or reboot NT altogether the memory used will start to be fairly close to the test server. It seeds to take almost a week for this to happen again but it looks like a memory leak. Has anyone else seen this?
I am running Back Office Server 4.5 service pack 5 and I have a memory leak with SQL 7.0. Anyone have any ideas?? I have a feeling it is related to having IIS 4.0 and SQL 7.0 on the same machine.
We are running 2 SQL 7.0 servers which have IIS 4 also installed. The servers are mirror images of each other and are configure the same(1 is test, the other is a production server) I have noticed that even if no one is on the production server the memory being used will peak at almost 100% of the physical memory and peg the CPU at 35-40%. If I shut down the SQL server or reboot NT altogether the memory used will start to be fairly close to the test server. It seeds to take almost a week for this to happen again but it looks like a memory leak. Has anyone else seen this?
Hi Everyone, We have a SQL server 6.5 machine that appears to be melting down. If I monitor the SQLSERVR process in Task Manager, I see the memory usage continue to climb. The number of threads and handles for this process also continue to climb. I am not able to see in SQL current activity what tasks might be doing this. Does anyone have any idea on ideas to troubleshoot this? The SQL server supports an Intranet application that connects to it from IIS via ODBC.
I am stumped on a apparent memory leak in SQL Server 6.5.
I am running a NT Server 4.0 with SQL Server 6.5sp3 and Cold Fusion Server 4.0. The database is used in tandem with CF4.0 to drive a website that offers a meeting search utility. The LIVE server has been experiencing enormous reductions in speed in the last week (the site has only been live for about 9 days). When the server was stopped/restarted the problems would disappear temporarily only to resurface within 1/2 hour.
On closer inspection of the of the Process tab in Task Manager SQLSERVR.EXE ' s Mem Usage climbs at a steady rate until it is utilizing so much of the available memory that the Cold Fusion Server process ends up pegging out (suspect because of forced page faults due to lack of memory resources?) which in turn slows the entire website down to a crawl. Even after the scripts have finished executing and SQLSERVR.EXE 's CPU% is 0 the memory that was allocated during processing is not released.
I have a NT Server 4.0 development server running SQL Server 6.5sp5a and CF4.0. I tried running same scripts on it as the live server and sure enough found the same scenario.
I also have a NT 4.0 Workstation here at home which has SQL Server 6.5sp5a and CF4.0. I have the DB replicated here. I can not reproduce the error on this machine running the very same scripts that are being run against the other two.
I have a CF .Net 2.0 SP2 app that I *think* is leaking memory. My application has a dozen or so forms, uses two compact flash cards (one for a custom radio and the other for a GPRS internet connection). We have both SQL CE 3.0 data files and xml files that are written to. And we are using log4net as our logging library. For communication with our custom CF card, we use the .NET SerialPort class. For GPRS communication we use HttpWebRequest class.
I've built a special test version of my app that accelerates the normal usage....basically accessing both CF slots continually, and showing a couple different forms.
After four or five days of my test app (which is about a year of real world usage), I see our SQL CE commands failing with "Not enough storage is available to complete this operation".
I've added some logging of the current memory using a p/invoke to GlobalMemoryStatus. What I see is that the MEMORYSTATUS dwAvailVirtual and dwAvailPhys are slowing decreasing over time, and when dwAvailVirtual reaches about 1 MB I start seeing the above errors.
I've started pulling things out of the app and still see the behavior. I've completely removed SQL CE and log4net and the same result happens.
I've done some profiling with .NET 2.0 SP2's Remote Performance Monitor, and what I see is that in a four hour test, the "Managed Bytes in Use After GC" and "Total Bytes in Use After GC" are not steadily going up. I see them fluctuate around the same levels. The heap snapshots are not showing me any red flags yet either. I see object counts going up and down over time. But in this same time period, I see that the values reported by GlobalMemoryStatus are gradually going up (about 1.5 MB more at the end of the test than the start). If I continue this test it eventually eats up all virtual memory in about 24 hours.
I'm going to continue pulling my application apart to narrow the problem down, but wanted to know if anyone out there has encountered similar behavior, and if you have, what did you find? Any ideas or tips on how to narrow this one down faster would be much appreciated.
I have a website based on ASP which is stored within an SQL database, however I appear to be experiencing a memory leak.
I'm running Windows Server 2003 x64 SP1 and MS SQL 2000 SP4.
The process which is consuming all my memory is sqlservr.exe *32. The process when restarted is about 27MB in size and will quickly reach 150MB or more until my server has no more memory.
I have little knowledge of MS SQL, however from what I've read, SQL by default is designed to use as much memory as possible, but not to the extent that it kills my server. By stopping and starting the sqlservr.exe *32 process memory is restored. I've scheduled a batch file to do this every morning at 5am as a temporary workaround. If this batch file didn't run, eventually I get the error message "HTTP/1.1 New Session Failed" when visting my website. Through my server web tools I'm forced to reboot my server which restores functionality of my site. I have also tried editing the way SQL handles memory in the Enterprise Console to a fixed amount of 118MB. I restarted my server, yet after two days the sqlservr.exe *32 process was using more than the set amount.
How can I trace the source of the problem? It's been mentioned to me open database connections may be causing the leak. How can I verify this step by step?
I believe that I may have a memory leak. I loaded a job into a scheduling engine and had it run every 15 minutes over the weekend. It ran successfully everytime untill sunday mid-day. That is when I started getting memory issues.
OnError,BPATLQDDW,NT AUTHORITYSYSTEM,Populate Flat Files,{B9F25310-0573-4042-A3DB-D0AEFD89D64C},{9E929349-B0FE-4EBB-A353-EC61805B4C3C},11/18/2007 8:16:16 PM,11/18/2007 8:16:16 PM,-1073450991,0x,The system reports 91 percent memory load. There are 3925336064 bytes of physical memory with 341479424 bytes free. There are 2147352576 bytes of virtual memory with 218148864 bytes free. The paging file has 8057835520 bytes with 4688982016 bytes free.
Are there any known issues with memory leaks that may be causing this? Does anyone have any suggestions? I have allready attempted to adjust the size of my default buffer.
I've encountered a problem regarding a buffer allocation failure whilst running a SSIS package. The package gets a recordset of values and then for each value it iterates through a for loop. Within the for loop it simply performs 5 data pumps, collecting data into staging tables and the processes the data. The staging tables are then cleared down and the loop restarts.
In the instance when we had the allocation failure we had an abnormally large number of values for which to iterate through the loop, which is why I think we don't generally see this problem.
I've been reading around an there are suggestions that there is a memory leak issue when using a for loop and this seems to tally with what I'm seeing.
Can anyone confirm that this is a problem and whether this has been fixed in any Service Packs.
We are currently running on 2005 9.00.3042.00 (32bit) - Standard Edition on Windows NT 5.2 - Service Pack 2
Hi I have recently installed SQL Server 7 I'm an NT and SQL newbie, so be gentle... After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a 'run out of virtual memory' error/message. Why is SQL bloating? I'm not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open). Help help help!
Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)
Thanks all.
PIII 256MB NT Server 4 option pack 4 service pack 4
I have saved a DTS package as a vb module (.bas) file and created and activex dll from it. When i use this dll in another program and watch the handles and memory usage of the process using task manager the handles and memory values keep increasing with every call to the DTS package. memory does not increase if the dts package has not been called.
I am using sql server 2000 and visual studio 6.0 (sp 4)
Last 2 nights (at night) my SQL Server has locked up, first night trying to back up MSDB(20 meg) and last night ran out of memory at 6:30 AM. No users on at either time, no jobs running on the second night. I was going to turn perfmon on tonite. Any input on what best to monitor?
I was doing a quick test with SQL Server Express and noticed that there is memory leak
Here is the pseudo code
do loop //1000 times { 1. Open connection to the database 2. Build SQL statement 3. Construct SQLDataAdapter 4. Fill DataSet using ( adaptor.Fill(dataSet); ) 5. Close connection to the database 6. Dispose whatever is disposable }
When I ran this test I saw that SQLServer service memory usage is going up and never released
Is there something that I am doing wrong. If I ran same test against Access database then there is no problem
I have a stand alone Java application that uses MS SQL Server 2000 andMicrosoft JDBC ODBC drivers to access it. Before I run the application,I open the task Manager to monitor the memory used by the SQL Server.Generally it begins 21 MB and very soon it reaches around 100 MB if Ido some operations in the application. The thing is that even if Iclose the application, the memory taken by SQL Server doesn't go down.What could be the reason behind it? Java code or MD drivers or SQLServer?I checked the code and we are closing the statements and result set andcommiting everywhere.Please let me know...I am trying to refactor as much as I can but stillout of luck.
We are using ODBC connection pooling with SQL Server 2000 v5 on Win 2000 v5sp4.Each time we get a connection, SQLConnect, we see the memory usage goup, about 52K. When we return the connection to the pool,SQLDisconnect, and after the time that the connection remains in poolexpires we see the memory usage drop by about 28K.Our basic steps for a select are:SQLAllocHandle(SQL_HANDLE_DBC, hGblEnv, &hdbc);SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT,(SQLPOINTER)lLoginTimeOut, 0);SQLSetConnectAttr(hdbc, SQL_ATTR_ACCESS_MODE,(SQLPOINTER)SQL_MODE_READ_ONLY, SQL_IS_POINTER);SQLConnect(hdbc,(SQLCHAR *)szDatabase, SQL_NTS,(SQLCHAR *)szUserId, SQL_NTS,(SQLCHAR *)szPassword, SQL_NTS);SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);// Build the select statementSQLExecDirect(hstmt, (SQLCHAR * )pszSQLStmt, strlen(pszSQLStmt));SQLFetch(hstmt);SQLFreeHandle (SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);Same memory leak occurs in DB2, but that's another forum.
In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.
The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.
Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?
I was doing a quick test with SQL Server Express and noticed that there is memory leak
Here is the pseudo code
do loop //1000 times { 1. Open connection to the database 2. Build SQL statement 3. Construct SQLDataAdapter 4. Fill DataSet using ( adaptor.Fill(dataSet); ) 5. Close connection to the database 6. Dispose whatever is disposable }
When I ran this test I saw that SQLServer service memory usage is going up and never released
Is there something that I am doing wrong. If I ran same test against Access database then there is no problem
Hi, there; I have a couple of issue that really frustrates me. I asked a similar question before but I haven't resolved it yet. My application was developed with VS2003, c#,CF1.0,Sp1.
I found that after my application runs for a couple of hours, my SqlCeDataAdapter.Fill() method throw exception "Error Code: 8007000E Message : Not enough storage is available to complete this operation.". I found a couple of threads on the website said that I have to dispose adapter object (including its command objects.) Yes, I did!!!. And I also dispose my SqlCeConnection before it is out of range and recreate it when I need it. See:http://www.tutorials-se.com/sqlserverce/Keeping-SqlCeConnection/
I also introduced hotfix from http://support.microsoft.com/Default.aspx?kbid=827837. It looks like I tried everything, but I still has this exception.
At the same time, From thread "http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=426640&SiteID=1", Mr. Ilya said "native DLL (e.g. bar code scanner API) " could cause AV (access violation) issue. Yes, my application is running on Symbol 9062B device which use barcode scanner API, sometimes application will have "0xc0000005" exception when it exists (not when it is running) A serious problem is that the laser randomly crash in the middle of the scanning without any exception. I am quite sure it is not code logic issue. This happens since I introduced two SqlCeConnections which point to two different .sdf files (run at background threads). My impression is that the memory or stack are corrupted for some reason.
Hopefully I make myself clear. So my question is: 1. Will Sp3 help this? (Of course I will do some test) 2. How can I know memory or stack are corrupted/How to trace.
Again, thanks Mr.Ilya, you do a great job here, you are really really helpful!
Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).
Is there a knwon bug like this? Is there a way to trace the ADO provider?
Hi, Situation: I have a web application that is running on a server. IT has told me that the application I developed is taking up enormous amount of ram. It seems that the amount of ram that is taken up, is slowly increasing. About 10 megs per hour (memory leak). Furthermore, he has informed me that the ram is taken up by the sql. My senior developer told me that I probably have sqlconnections open that are not closed. I have checked through the application and it seems they are all closed properly. But I may have missed some, as the application is pretty big. Questions: 1. Is there an application or piece of code that can monitor how many sql connections I have open during runtime? 2. Could there be any other common causes to such a kind of memory leak. Any help with this matter would be very much appreciated. Thank you for your time. Sincerely, Jeff
I have a couple of client programs continuously dumping data into a SQL 7.0 database. I use ODBC connections that remain open as long as the client programs are running.
The memory consumed by SQL grows until all the system memory is consumed at which point the clients freeze (SQL no longer responds). Restarting SQL Server "cures" the problem for a few hours.
I've tried limiting the amount of memory used by SQL, but this only accelerates the eventual client freeze-up. At the moment, I let SQL dynamically consume memory.
On one of our intranet SQL servers running under Windows 2000 SP 3, MSsql 2000 SP4 seems to gradually "eat" away all available memory (with no obvious reason for it) until a certain limit is reached, forcing the server to slow down substantially since the OS has to SWAP continuously.
I would appreciate any suggestions at this point on how to tackle this problem :)
Hi everybody !I´m maintaining a large intranet (approx 10000 concurrent users) running onone IIS box and one DB box with sqlserver 2000.Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed onthe db box.Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem tobe using it all.Currently SQLSERVER 2000 crashes at least once a day.Its very weird, I run performance monitor with counters on, memory, diskusage, num users, locks and such.There is no indications in the counters before the crashes, they just happenvery sudden.Only indication is that sqlserver makes some huge jumps in memory usage andmostly the sqlserver then crashes an hour or 2 later.The only thing that peaks a lot are the locks/sec counter.My analysis of disk usage, queues etc. tells me i got no kind of i/obottlenecks.Can anybody give me a clue as to what i should do ?Best regards, Thue
Hello,I am having trouble with a production db server that likes to gobbleup memory. It seems to be a slow burn (maxing out over about an 18hour time frame, before pegging both procs on the server and bringingeverything to a standstill). After viewing the trace logs, it appearsthat all the SPIDs are being recycled - does this assert thatconnections are being properly closed when the need for them hasended? The code base is huge and quite messy, so it's difficult todiscern where the problem is just by looking at code, and we can'tseem to nail it down by looking at it, and I'm not sure what to lookfor in the trace logs or perfmon.Does anyone have any suggestions about what else might cause such aproblem?Ryan
