Database memory leak

[MikeBl]

I'm running Labview 2009 with the database connectivity toolkit, connecting to a SQL server (2008 r2) using SQL native client 10.

I'm calling a stored procedure that returns about 200 records, each of 7 fields. This is a dynamic database being updated once per second from another computer, so I have to call this stored procedure once per second as well.

The problem is memory usage just keeps increasing, eventually exceeding 1.4Gb and crashing the application (takes several hours to do this). This happens whether the application runs under XP, Vista(32) or Windows 7(64).

I have built a single VI application that simply opens the database connection, then once every 2 seconds executes a SQL request for the data (not the stored procedure), fetches the recordset and discards it, and frees the recordset reference. Again the memory usage increases continuously.

I ran this VI from LabView with the memory profiler running, none of the VIs memory usage figures changed over time, but the total memory used by LabView increased.

I also built the main application without the database connection, simulating the data, and this ran with no memory increase.

Any ideas?

[MahdiehG]

Hi Mike,

Does this article describe what is happening in your code: http://digital.ni.com/public.nsf/allkb/862567530005F09E862568020079A9B7?OpenDocument

Please can you send some more information about exactly what VIs/functions you are using.

Thanks

[Yamaeda]

Do you open and close the DB ref every time?

/Y

[MikeBl]

MahdiehG:

The article you refer to (I had already found it) does not really apply. I'm using a SQL database and only reading from it.

I have attached the VI I am using to investigate this problem.

Sequence0 - Find the directory for the UDL file, dependent on application or VI in LabView.

Sequence1 - Use the UDL file to open the connection.

Sequence2 - Every 2 seconds, submit the SQL request and fetch the data, free the recordset handle, ignore the data, check for stop.

Sequence3 - Close the connection.

The recordset returned contains 150 to 200 records each with 7 fields.

Yamaeda:

Currently I am not opening and closing the DB ref every time, but I have tried doing so previously with no noticeable difference.

Thank you for your replies.

Mike.

[MahdiehG]

Hi Mike,

Thanks for sending your code over. I don’t have your exact database set up to be able to check it on my end however I have a few suggestions that you can try out.

1)      Have you tried using a standard case structure instead of a stacked sequence structure?

2)      Define the Cache Size input of the DB Tools Execute & define the curser type to be Dynamic

3)      Put the DB Tools Free Object in case 3 just before you stop the connection

4)      Can you try running the official example and monitor the memory usage: NI Example Finder>> Toolkits and Modules >> Database Connectivity >> Database Fetching

5)      What type of database are you trying to communicate with? e.g. Oracle, Access, Ingres etc

Please let me know how you get on.

Thanks,  

[Yamaeda]

I've cleaned it up some. Things to note and test:

You can enable/disable the structure to test your query or the complete Select-query. Does it make any difference?

Does it make a difference if you have an indicator to send the result to or not? (as in my query)

/Y

[MikeBl]

MahdiehG:

I'm using SQL server (2008 r2) using SQL native client 10, so the database is SQL.

I still have had no success in locating the memory leak.

I believe the problem lies either with the database toolkit, or the SQL native client 10 as the memory profiler does not show any VI's leaking memory, just the program as a whole leaks memory as shown by Task Manager. Changing the program has not eliminated the memory leak.

Yamaeda:

 I tried this previously with an indicator, it was no better.

I'm using LabView 2009, although I have an evaluation copy of 2010 available. In the real application I actually have to call a stored procedure passing some parameters, hence my use of the execute and fetch VIs. I am trying your version on 2010 evaluation.

Thank you both your help.

I am working on a workaround that involves automatically terminating the program and restarting it every 10 hours to recover the lost memory.

Mike.

[MahdiehG]

Hi Mike,

Did you try one of the official examples to see if they cause a memory leak as well?

We need to find out exactly where the memory leak is coming from. Please can you open your VI and go to Tools >> Profile >> Performance and Memory. You will need to tick Profile memory usage and memory usage. We need to look at the difference between Min Bytes and Max Bytes column. This difference should be as close to zero as possible otherwise there is memory allocation or de-allocation happening. You will need to let your VI run for a while so we can get a clear understanding of what is happening.

I have also had a go at modifying your code. Please can you try the attached and let me know if it helps at all or not.

Kind regards,

[MikeBl]

I have now run my VI with the memory profiler and Windows Task Manager.

Task Manager reported the following memory usage for LabView:

Time    Working     Memory(private)   Commit Size

Start    130796      87996                  95656

2 mins  131084     88260                  96056

5 mins  131484     88376                  96064

10 mins 132792    88496                  96060

30 mins 132960    88664                  96148

The biggest jumps in usage occured when the amount of data fetched from the database varied significantly, although memory usage slowly increased even with no data fetched. If the amount of data returned was reduced, and then increased memory usage usually increased when the data size increased.

From the profiler, the only vi to show any difference between min bytes and max bytes was:

NI_Database_API.lvlib:Rec Fetch Recordset Data (R).vi

min 34.75k  max 313.14k

While the recordset size fetched was constant, these were both 313.14k, only when the recordset size reduced significantly did the min value reduce.

The largest, and most frequent, record set fetched consisted of 108 rows, each containing seven items.

For all the other VIs the min and max values were equal to each other, the largest of which was 10.21k.

I have a workaround in place, although this is not ideal. I am monitoring the total private memory usage from within the program. If the usage exceeds 350Mb I terminate and restart the program in the middle of the night, if it exceeds 550Mb I terminate and restart immediately. When first started the program uses about 60Mb. As you can see from these values the memory leak is significant to cause this much memory to be consumed. If I build and run a version of the program without the database access, just simulate the data, the memory usage does not increase.

I will carry out further tests on an 'official example', and the modified VI you attached.

Mike.

[Joachim082]

I also the memory problems - after switching to the Ado-Toolkit (http://www.ib-berger.com/index.php?action=adotool_en) my problems with the database connectivity toolkit disappeared. Maybe you give it a try.