Posted on 02/04/2024 13:49:59
Hi Anders,
The problem you encounter is usually an initial issue: when cleanup has not been performed at all, and you need to clean up the first time. I don't think we will be doing any changes to this, as the issue lies beyond the timeout itself: you also risk a transaction log going wild (disk space and memory), and you overall risk a huge overload.
When we run into solutions with this issue, we choose to handle it manually.
I use
---
SELECT
t.NAME AS TableName,
p.rows AS RowCounter,
SUM(a.used_pages) * 8 / 1024 / 1024 AS UsedSpaceGB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
SUM(a.used_pages) desc
---
to obtain a list of tables, which sorted by size may indicate what needs cleaning up.
With GeneralLog as example:
---
DECLARE @year int
SET @year = 2018 --start this year
DECLARE @month int
SET @month = 1 --
DECLARE @day int
SET @day = 7 -- leave this alone. Used for deleting in chunks of 7 days to avoid log from exploding
WHILE (@year < 2024)
WHILE (@month <= 12)
BEGIN
WHILE (@day <= 35)
BEGIN
DELETE FROM GeneralLog
WHERE YEAR(LogDate) = @year
AND MONTH(LogDate) = @month
AND DAY(Logdate) <= @day
SET @day = @day + 7
END --day
SET @day = 7
SET @month = @month + 1
END --month
SET @year = @year + 1
END --year
---
This will delete in chunks of 7 days, avoiding the overload on log and memory.
BR
Snedker
