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 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?
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, 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
When you call IRowsetIndex::GetIndexInfo, it allocates memory for an array of DBINDEXCOLUMNDESC structures, which the documentation says to free using IMalloc::Free. However it does not say to free the pColumnId member of each DBINDEXCOLUMNDESC (which is a DBID*), or the pointer members of pColumnId. I think that if you don't do this, you will have a memory leak.
I used the following code to completely free the DBINDEXCOLUMNDESC structures:
Code Block inline void FreeDBIDMembers(DBID &dbid) { switch(dbid.eKind) { case DBKIND_GUID_NAME: case DBKIND_NAME: if(dbid.uName.pwszName != NULL) CoTaskMemFree(dbid.uName.pwszName); break; case DBKIND_PGUID_NAME: if(dbid.uName.pwszName != NULL) CoTaskMemFree(dbid.uName.pwszName); if(dbid.uGuid.pguid != NULL) CoTaskMemFree(dbid.uGuid.pguid); break; case DBKIND_PGUID_PROPID: if(dbid.uGuid.pguid != NULL) CoTaskMemFree(dbid.uGuid.pguid); break; } } // Used to free memory allocated by IRowsetIndex::GetIndexInfo inline void FreeDBINDEXCOLUMNDESC(DBINDEXCOLUMNDESC *pColDesc, ULONG nColumns) { if (!pColDesc) { return; } DBINDEXCOLUMNDESC *pColCur = pColDesc; for(unsigned int i = 0; i < nColumns; ++i, ++pColCur) { if (pColCur->pColumnID) { FreeDBIDMembers(*pColCur->pColumnID); CoTaskMemFree(pColCur->pColumnID); } } CoTaskMemFree(pColDesc); }
I used CoTaskMemFree rather than IMalloc::Free as I am working with Pocket PC. Thanks also to Joao Paulo Figueira who has documented a fix to a similar memory leak, which was my inspiration for finding and fixing this one! If you are using Figueira's CRowsetIndex, part of his adapted ATL OLEDB templates for Pocket PC, then you are likely to have this leaky code too.
Hi, The server is on win 2003 server with sql server 2005 workgroup sp2 ram is 2 GB. the SQL start with 100MB and 100MB VM and after two days of regular and not massive work the SQL memory goes up to 1GB ram and 1GB VM
how can i know who is the process that cuase the SQL memory to incress
is it somthing usual for sql 2005 to go up to 2 GB mem?
DBPROPSET dbpropset[2]; // Property Set used to initialize provider DBPROP dbprop[2]; // property array used in property set to initialize provider DBPROP sscedbprop[1];
// Create the SQL Server CE provider hr = CoCreateInstance(CLSID_SQLSERVERCE_3_0, 0, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)&pIDBInitialize);
// Initialize a property with name of database dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE; dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED; dbprop[0].vValue.vt = VT_BSTR; dbprop[0].vValue.bstrVal = SysAllocString(lpszDBName);
// Specify the property with name of the database dbprop[1].dwPropertyID = DBPROP_INIT_MODE; dbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED; dbprop[1].vValue.vt = VT_I4; dbprop[1].vValue.lVal = DB_MODE_READWRITE;
// Specify the property for TRANSACTION COMMIT MODE --------- it seems does not work // sscedbprop[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE; // sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED; // sscedbprop[0].vValue.vt = VT_I4; // sscedbprop[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH; sscedbprop[0].dwPropertyID = DBPROP_SSCE_FLUSH_INTERVAL; sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED; sscedbprop[0].vValue.vt = VT_I4; sscedbprop[0].vValue.lVal = 1;
// Initialize the property set dbpropset[0].guidPropertySet = DBPROPSET_DBINIT; dbpropset[0].rgProperties = dbprop; dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]); //Initialize property set DBPROPSET_SSCE_SESSION --------- it seems does not work // dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION; // dbpropset[1].rgProperties = sscedbprop; // dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]); dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT; dbpropset[1].rgProperties = sscedbprop; dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]);
// Set the properties into the provider's data source object. hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties); if(FAILED(hr)) { goto CleanExit; }
Hi, I am using a For Loop container with a script inside it in My SSIS package. Inside the script I am type casting one of the object variables in a class object of XMLReader Type. I am using CType Function for it. The For Loop is resulting in a memory leak and the references are not getting deleted even if they go out of scope. Does anyone have faced similar issue? Please guide.
when i'm consistantly updating database on the server, I've noticed that the memory usage goes higher and higher. How can I prevent this? Or is there a way to prevent this other than turning off the computer time to time?
Local SQL 6.5 SP4 on a Windows NT SP4 workstation (Pentium 350 / 128 MB). SQL server has 32 MB of RAM. It starts well but begins continuously eating up virtual memory by 16k blocks in a second, until it takes all virtual memory.
Server's shutdown generate following error in Event log System section:
Event ID: 7011 Source: Service Control Manager Description: Timeout (120000 milliseconds) waiting for transaction response
When I start Server from command prompt with -f switch, it did not eat virtual memory, and did not generate any error in Event log at shutdown.
Can anyone give me some sugguestions here.Connection is declared at the start of the applicationSet rsFZReport.ActiveConnection = connThen a function repeatly opens recordsets like this -rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText....reporting code etc...If rsFZReport.State > 0 ThenrsFZReport.CloseSet rsFZReport= NothingendifUsing VB Watch debugger, the close and set nothing do NOT release anymemory. It just keeps increasing. The application connects to multipledatabases and many tables, so its uses about 10Mb/minute.(I don't think its of relavance but Form.show uses memory but form.unloaddoesn't release any back)I've installed SP3a and MDAC 2.8 to no avail. Anyone have any ideas or knowwhat I can do?msado27.tlb 2.80.1022.0MSSQL Server SP3a (3 has a memory leak apparently)VB6, SP6Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4server/workstation (they are both and the same) to thisSqlsrv32.dll 2000.85.1022.0doesn't help.Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;UserID=uid;Password=thepwd;OPTION=3;connect timeout=240;
