Could you implement the same trigger functionality via a stored procedure?


>I am running SQL Server 2000 on a windows 2003 Server.
>I have a DTS package that imports a text file into a table via BULK INSERT.
>The table has a trigger on it that furhter calculates values based on the data inserted and inserts these new values into another table. The import occurs every minute and takes less than a second. What happens in the trigger however takes about 15 seconds as I'm calculating a lot of values off the data inserted - so the entire DTS package completes in about 16 seconds total. This is plenty fast. However, every time the package runs SQL uses a little more memory until ultimately, 45 minutes later, the memory is maxed out. At that point the package takes up to 70 seconds to run, whjich is completely unacceptable. If I stop and restart SQL everything is fine again, for another 45 minutes anyway.
>
>My server has 2 Gig of RAM and isn't running any other applications. I have it configured to use a 'fixed Memory' of 1500M and also checked the 'Reserve Physical Memory for SQL Server' option. I've also tried Dynamic Memory Allocation with the same result.
>
>My question is this...How do I know how much RAM is enough? I can't afford to buy 2 Gig more RAM if it's only going to prolong the problem another hour or two. Is there any way to determine how much RAM I need or, better yet, dynamically flush the cache so SQL will behave as it does when I first start it up?
>
>Thanks in advance and let me know if more information would help.