Как очистить SqlDependency из памяти SQL Server?

Как очистить SQL Server, чтобы избавиться от объектов SqlDependency с истекшим сроком действия? После того, как я получу событие от объекта SqlDepedency, мне нужно создать новый, прежде чем я смогу получить новое событие. Однако использование памяти процессом SQL Server увеличивается до тех пор, пока не исчерпается разрешенная память (SQL Server Express). Как избавиться от старых запросов?

Код:

// Func: RegisterTableListener
using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DatabseEventConnectionString))
{
if (cmd == null)
{
    cmd = cn.CreateCommand();

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT HostName, LastStatus, LastDetails, xml FROM dbo.[SystemTable]";
}

lock (cmd)
{
    cmd.Connection = cn;
    cn.Open();
    cmd.Notification = null;

    //  creates a new dependency for the SqlCommand
    if (dep == null)
        dep = new SqlDependency(cmd);
    //  creates an event handler for the notification of data
    //      changes in the database.
    dep.OnChange += new OnChangeEventHandler(dependency_OnChange);


    using (SqlDataReader reader = cmd.ExecuteReader())
    {
    // code here to read
    }
}
}

// Func dependency_OnChange
//SqlDependency dep = sender as SqlDependency;
dep.OnChange -= dependency_OnChange;
RegisterTableListener();

person JeremyK    schedule 14.12.2011    source источник
comment
Как вы создаете объекты SqlDependency? Пожалуйста, разместите свой код. Правильно ли вы их утилизируете?   -  person Oded    schedule 15.12.2011
comment
Я обновлю свой комментарий кодом, когда завтра приду на работу. Sudo: SqlDependency dep = new SqlDependency (cmd); dep.OnChange += весело; SqlDependency не реализует IDisposable   -  person JeremyK    schedule 15.12.2011
comment
Я обновил код. Даже когда я запускаю только один экземпляр SqlDepdency и каждый раз вызываю Stop and Start, память увеличивается. Я понятия не имею, что происходит.   -  person JeremyK    schedule 15.12.2011


Ответы (1)


Существует определенное поведение класса Microsoft SqlDependency. Несмотря на то, что вы вызываете метод SqlDependency.Stop(), освобождаете SqlCommand и SqlConnection, он по-прежнему сохраняет группы диалогов (sys.conversation_groups) и конечные точки диалогов (sys.conversation_endpoints) в базе данных. Похоже, что SQL Server загружает каждую конечную точку диалога и использует всю разрешенную память. Вот тесты, подтверждающие это. Итак, чтобы очистить все неиспользуемые конечные точки диалога и освободить всю занятую память, вам нужно запустить этот код SQL для своей базы данных:

DECLARE @ConvHandle uniqueidentifier
DECLARE Conv CURSOR FOR
SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP
WHERE CEP.state = 'DI' or CEP.state = 'CD'
OPEN Conv;
FETCH NEXT FROM Conv INTO @ConvHandle;
WHILE (@@FETCH_STATUS = 0) BEGIN
    END CONVERSATION @ConvHandle WITH CLEANUP;
    FETCH NEXT FROM Conv INTO @ConvHandle;
END
CLOSE Conv;
DEALLOCATE Conv;

Также SqlDependency не дает вам возможности получать ВСЕ изменения таблицы. Таким образом, вы не получаете уведомления об изменениях при переподписке SqlDependency.

Чтобы избежать всех этих проблем, я использовал другую реализацию класса SqlDependency с открытым исходным кодом — SqlDependencyEx. . Он использует триггер базы данных и собственное уведомление Service Broker для получения событий об изменениях таблицы. Это пример использования:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Надеюсь это поможет.

person dyatchenko    schedule 26.01.2015
comment
что означает эта строка ГДЕ CEP.state = 'DI' или CEP.state = 'CD'. что ты пытаешься сделать. пожалуйста, помогите мне понять. Благодарность - person Mou; 25.04.2015
comment
означает ли это, что если я использую этот sql WHERE CEP.state = 'DI' or CEP.state = 'CD', то старый разговор будет удален? - person Mou; 25.04.2015
comment
@Mou Вот еще одно свидетельство утечки памяти и комментарии изобретателя по этому поводу. В конце концов, вы можете найти похожее решение. groups.google.com/forum/#!topic/ А вот модульные тесты, которые показывают, что SqlDependency хранит незакрытые или отсутствующие конечные точки диалога в базе данных. github.com/dyatchenko/ServiceBrokerListener/blob/master/ - person dyatchenko; 25.04.2015
comment
@Mou Статья от Rusanu, о которой я упоминал выше, дает пример очистки старых конечных точек разговора только с состоянием CD. Но по моему опыту DI конечные точки также остаются в базе данных. - person dyatchenko; 25.04.2015