WSUS — Reset Server Node — SqlException Execution Timeout Expired

Ох уж этот WSUS, столько времени съел за совместную с ним 15 летнюю жизнь. В очередной раз БД SUSDB разрослась до 52GB, очистка сервера через MMC консоль не работает до конца и прерывается с сообщением System.Data.SqlClient.SqlException — Execution Timeout Expired. Invoke-WsusServerCleanup -CleanupObsoleteUpdates обрывается с абсолютно таким же сообщением и временем выполнения. Сама БД WSUS расположена на отдельно стоящем MS SQL Server 2014, БД жива, во время операции по очистке грузит процессор и диск на полную. Я так понимаю, приложение WSUS сервера не дожидается окончания выполнения одного из запросов и сваливается в Timeout.

Расследования, поиск информации, пробы всего и вся были крайне долгими. Таймауты, лимиты и прочее были либо убраны, либо поставлены в максимально возможные значения, но это не помогало. Скрипт перестройки индексов выполнялся. Начал смотреть, откуда такой большой размер у БД. Выяснилось, что таблица tbEventInstance занимала больше половины занятого пространства, через SQL Server Management Studio открыл свойства таблицы:

Index Space 1 916,078 MB
Row Count 7 720 991
Data Space 39 325,508 MB

Недолгий поиск в интернете привёл к статье https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/909131 в которой достаточно доходчиво разъяснено: одна из причин ошибки связана с тем, что содержимое таблицы tbEventInstance превысило 1 миллион записей:

This problem occurs if the number of reporting events in the tbEventInstance table exceeds 1 million rows.

A WSUS server that is using the recommended hardware can support a maximum number of 15,000 clients by using a default detection cycle of 22 hours. The number of reporting events that is added to the tbEventInstance table depends on the number of clients and on the frequency that is set for each detection cycle. Automatic deletion of rows from the tbEventInstance table starts when a client tries to send a report. The automatic deletion process is initiated only if the reporting events in the tbEventInstance table exceed 1 million rows.

The automatic deletion process is very slow and blocks the client computers from reporting back to the WSUS server. By default, WSUS is configured to delete events that are older than 15 days on workstations and that are older than 90 days on servers. WSUS deletes old events at the rate of 1,000 events every 12 hours.

Видимо в механизме очистки что-то застряло, либо некорректно настроено.

Остановил сервер WSUS и веб сервер: WSUS Service и World Wide Web Publishing Service.

Запустил очистку таблицы:

USE SUSDB;
TRUNCATE TABLE tbEventInstance;

Запустил «уменьшение» БД, чтобы освободить, хотя бы временно, место на диске:

DBCC SHRINKDATABASE ('SUSDB');  

БД стала занимать 5.5 ГБ вместо 52 ГБ.

Запустил перестроение и реорганизацию индексов (скрипт от MS):

USE SUSDB; 
GO 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
 
SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO

Запустил сервисы WSUS Service и World Wide Web Publishing Service

Информация о синхронизациях исчезла, запустил заново, прошла быстро и хорошо, нашла 5 новых обновлений и пометила 5 как просроченные.

Результат, как говорится — на лицо, БД стала занимать меньше места, процедура очистки сервера прошла достаточно быстро и без ошибок.

Ниже приведу некоторые опции и запросы, которыми пользовался.

Посмотреть топ 10 выполняющихся запросов в БД

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Узнать количество «ненужных» обновлений

USE SUSDB 
GO 
EXEC spGetObsoleteUpdatesToCleanup

Изменить или убрать таймаут выполнения запросов

USE SUSDB;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;

Изменить или убрать таймаут в пуле IIS

Get-Module WebAdministration
Import-Module WebAdministration

Get-ChildItem IIS:\AppPools | ? name -eq "WsusPool" |
select name, @{Name="Timeout"; Exp={$_.processmodel.idletimeout}} 

Set-ItemProperty IIS:\AppPools\WsusPool -Name processModel.idleTimeout -Value "00:00:00"

Изменить или убрать ограничение используемой памяти в пуле IIS

Get-ChildItem IIS:\AppPools | ? name -eq "WsusPool" |
select name, @{Name="Memory"; Exp={$_.recycling.periodicrestart.privateMemory}}

Set-ItemProperty IIS:\AppPools\WsusPool -Name recycling.periodicrestart.privateMemory -Value 0