History Cleanup Task issue
-
Hi all
We have setup History Cleanup tasks at clients to clear Maintenance plan and SQL Agent History but something odd has been picked up by one client. I would like you advice asap.
The maintenance plan is running as expected but they advised that the txt files in c:\Program Files\Microsoft SQL Server....\Log folder are still there. Is the maintenance plan not supposed to by default delete those files as well?
Or this the issues maybe a rights issues. We are running the maintenance plan using a sql sysadmin user.
Andre
-
This was removed by the editor as SPAM
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I also tried testing with a Windows authentication /AD user with admin rights on the server, this made no difference. it was owner of the Maintenance Plan and executed the MPlan and the Text files were not deleted.
-
This was removed by the editor as SPAM
-
Should I be logging this as a Bug with Microsoft?
-
So, the maint plan in general runs under the Agent account. Did you change that to an account with rights to the folder? The user running the plan doesn't matter, it's a Agent.
Are these .txt files from the maintenance plans? I can't remember if these are supposed to be removed, but I thought that it was only cleaning data in tables and .bak/.trn files
-
Hi Steve
Image 1,-- this maintenance plan is only for SQL Agent and Maintenance plan History, not for .bak and trn history
Image 2 -- On SQL Agent you will see I linked it to a domain user with full admin rights on the server, including rights to deleting files on the folder in question
Image 3 -- these are the txt files are they appear in the folder , I am testing currently on SQL 2022 Developer, but it basically the same issue on SQL 2019. We have the issue happening at a client with SQL2019 Enterprise and I expect will we have it at all our SQL2019 and 2022 clients.
The client complained that when viewing history of SQL Agent jobs it just started hanging for hours even with the History cleanup Maintenance plan running and cleaning once a month clearing the data older than a month
Once I manually deleted the old txt files, about 50000 tx files they no longer experience the slow response on viewing SQL agent history.
Image 4 is just the jobs we have setup locally to confirm that the Backup job name is the same as the txt files
Please let me know if you need anything more
Andre
-
I haven't used maintenance plans for years, but this link suggests that you need to explicitly define the log files to delete.
https://www.mssqltips.com/sqlservertip/3225/sql-server-maintenance-plans-reporting-and-logging/
-
The history cleanup task should just run (date changed) this:
EXECUTE msdb..sp_maintplan_delete_log null,null,'2024-10-23T11:19:52'
This is in msdb, and doesn't look like it deletes files, only table entries:
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_maintplan_delete_log] Script Date: 10/23/2024 12:25:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_maintplan_delete_log]
@plan_id UNIQUEIDENTIFIER = NULL,
@subplan_id UNIQUEIDENTIFIER = NULL,
@oldest_time DATETIME = NULL
AS
BEGIN
-- @plan_id and @subplan_id must be both NULL or only one exclusively set
IF (@plan_id IS NOT NULL) AND (@subplan_id IS NOT NULL)
BEGIN
RAISERROR(12980, -1, -1, '@plan_id', '@subplan_id')
RETURN(1)
END
--Scenario 1: User wants to delete all logs
--Scenario 2: User wants to delete all logs older than X date
--Scenario 3: User wants to delete all logs for a given plan
--Scenario 4: User wants to delete all logs for a specific subplan
--Scenario 5: User wants to delete all logs for a given plan older than X date
--Scenario 6: User wants to delete all logs for a specific subplan older than X date
-- Special case 1: Delete all logs
IF (@plan_id IS NULL) AND (@subplan_id IS NULL) AND (@oldest_time IS NULL)
BEGIN
DELETE msdb.dbo.sysmaintplan_logdetail
DELETE msdb.dbo.sysmaintplan_log
RETURN (0)
END
DELETE msdb.dbo.sysmaintplan_log
WHERE ( task_detail_id in
(SELECT task_detail_id
FROM msdb.dbo.sysmaintplan_log
WHERE ((@plan_id IS NULL) OR (plan_id = @plan_id)) AND
((@subplan_id IS NULL) OR (subplan_id = @subplan_id)) AND
((@oldest_time IS NULL) OR (start_time < @oldest_time))) )
RETURN (0)
ENDI think if you use the maintenance plan cleanup task, then this runs something like:
EXECUTE master.dbo.xp_delete_file 1,N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log',N'txt',N'2024-09-25T12:27:08'
though I think it was limited in what it can delete. Supposedly you can delete maintenance plans files this way, but IIRC is was flaky.
I'd use another step in my job with PoSh to do this cleaner.
-
Thanks Ed and Steve.
I should have looked further into the other maintenance plans. They are doing the job. Just a pity to have to do 2 maintenance plans to clear maintenance plan history, though it does make sense in a way. With this the issue then is resolved
Andre
-
This was removed by the editor as SPAM
-
You don't need two plans. These are tasks and you can have mulitple tasks in one plan.
-
Agreed, I set them up in the same maintenance plan
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply