"Don Miller" <millerdf@hotmail.com> wrote in message
news:%23uAOY40UEHA.3944@tk2msftngp13.phx.gbl...
> I have a small C program that uses the DBlib routines to send around 40
> transactions per second to a SQLserver 2000 database. The 40 transactions
> are either INSERT or UPDATE statements to one of about five different tables
> in the database.
>
> When I run the SQLserver Profiler, I typically see between 7 and 9 lock
> acquired and lock released events per transaction. Why is this happening,
> is it normal, and, if not, what can I do to minimize it?
>
> The reason I'm asking stems from a weird problem. The application runs just
> fine for about an hour. But at each hour interval after the start of the
> application, I see the transaction count drop from the normal 40 per second
> to 1 or 2 per second for at least 2 or 3 minutes. I am queueing up
> transactions while this block occurs, and sometimes I can get over 7000
> transactions queued before things get back to normal. Sometimes I must kill
> the application and restart it to get things back to normal.
>
> When the application is blocked, I can use the SQL Enterprise Manager to
> view current activity and I see that my application has several locks active
> on the database (sometimes the file and sometimes the page). These locks
> can remain even if I kill my application using the Windows task manager.
> When that happens, even restarting my application is fruitless. But, if I
> kill the process using SQL Enterprise Manager, at least sometimes it appears
> that I've broken the "log jam" and things return to normal after I restart
> the application.
>
> What in the world is going on here? The one hour symptoms occur with or
> without the SQLserver Agent running (I thought at one time that some system
> job might be interfering).
>
> Thanks for any insights you might have...
> Don Miller
>
>