General information
Forum:
Microsoft SQL Server
Category:
Database management
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.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only