gdraghetti (7/24/2012)

---

Hi,

I have a problem in SQL2005 so after several days of work without problems, suddenly takes a very slow (sqlserv.exe works much CPU in task manager) and checking the situation I realize that the database is growing and simultaneously a specific queue of messages is not empty anymore but only grows.

In the past I had a similar problem but the cause was not explicitly a ROUTE for each queue (note that the RECEIVE command has RETENTION OFF).

I think that the causes of the current problem could be two:
Hypothesis 1: Failure to empty the queue causes the increase of database size.
Hypothesis 2: the database is in normal expansion and this causes the slowdown with the consequence of permanent posts in the queue.

Hypothesis 1:
I state that I use several queues (33 for different events) and that the problem is always on the same queue (which is the most traffic).
Maybe I could work on some of these fronts:
a) Increase TIMEOUT (currently 500 for all queues) in the RECEIVE command.
b) Increase MAX_QUEUE_READERS (currently 2, the number of cores in PC, for all queues)


Hypothesis 2:
I could act in the creation of the database specifying appropriate options SIZE and FILEGROWTH.
a) It may make sense to immediately define SIZE as the size that is thought to have the database (eg. in a year)?
b) It may make sense to give a SIZE of the log file as half of that applied to the database? Or to avoid misunderstandings reserve the same size for both?
Note that my procedures do not use statement BEGIN TRANSACTION / COMMIT TRANSACTION.
c) For performance it is better to set the FILEGROWTH in MB or percent? (On internet I read mixed reviews and I'm doing some tests, I think it would make more sense to increase the percentage even though I often read to the contrary).


Thanks to those who can give me some help