Can Change Notification cause unnecessary server load or log file issues?

[bryn.ball]

Hi team.

A problem is occurring daily with our SQL Server (Express) DBM where the server becomes unresponsive to applications that use SDAC queries and the SDAC ChangeNotification component.

The two applications in particular have notification on a single (main) query, displaying and editing data via a data aware grid. Users can only edit certain fields of that query and there is no delete, append or insert activity permitted. The MutipleActiveResultSet and MultipleConnection options are true on the DB connection.

I am unsure if the behaviour is attributed to the fact that we are using default SDAC change notification methodology or something else entirely but this is the one difference (in comparison to the two other applications that use the sql server).

Each of the applications addresses two other databases on the server but change notification only applies to the main database that the application is maintaining. One of the applications updates, deletes and inserts records in a second database by way of TMSSQL components.

I am certain, after a significant period of monitoring, that all transactions are committed properly and am at a complete loss as to why the server is behaving this way. I realise the problem could well be nothing to do with SDAC (in general) or change notification but wonder if you have encountered this sort of issue before and have any suggestions as to cause/rectification. I also wonder if, in conjunction with the above, our recent (significant) increase in the number of connections to the server and databases could contribute to the issue.

When I look at open transactions on the server from the sys.sysprocesses table (open_trans = 1) I note that there is a "broker_receive_waitfor" entry with cmd "DELETE" in a suspended state for each user connecting to the two applications. The timeout is 5 days (the default for TMSChangeNotification) and wonder if the time period could have anything to contribute to the issue.

WIth regard to TMSConnection.AutoCommit and updates/deletes executed via TMSSQL components, it appears that the implicit DB transactions encapsulating these updates are committed automatically. Can you please confirm?

At present, our only recourse currently is to restart the SQL server instance.

Any suggestions/advice you can offer would be appreciated.

Kind regards

Bryn

[Stellar]

To get messages from the queue, TMSChangeNotification executes a query that returns the results when news messages appear in the queue. The query creates a session on the server that awaits new messages. To delete a session, you can terminate the running TMSChangeNotification, e.g.

MSChangeNotification1.Enabled := False;