Written by Sherry Kissinger.

ConfigMgr Truncate History Tables

Thanks very much to Umair Khan, Twitter @TheFrankUK, for the assist! One of the hiccups recently was making sure to exclude "globaldata" type HIST tables, so that DRS replication doesn't want to go into MAINTENANCE_MODE and re-initialize global data.

Have you ever noticed, being the extreme ConfigMgr geek that you are, that you have v_gs and v_hs views? Which point back to current, and History tables in your database.

Have you ever, and I mean EVER, needed to reference anything in the v_hs views? Ever? If you have, then perhaps this isn't for you. If you've never used the data in the history views... why are you keeping it? Sure, there are Maintenance Tasks you can tweak to help keep that data down, but... there is a quick (not supported) way to clean that up.

Keeping in mind this is NOT SUPPORTED (but it works anyway), so do this at your own risk, etc. etc. If you mess up, I don't support you. Microsoft won't support you. You have a backup of your database, right?

On your Primary Site (even if you have a CAS, you still do this at your primary sites), all of this is done in SQL, the console is not involved at all.

Take the below, and in SQL management Studio, just take a look at how much History data you have. Only you can determine if that's cause for concern, and you want to automate cleaning that up using a SQL Truncate process. At my company, in the 12+ years that people on this team have been supporting SMS, then ConfigMgr...no one ever needed data in the History tables. So...for us this was a lot of space gained, that didn't need to be backed up, and made nightly processing of some of the maintenance tasks that look at history tables finish MUCH faster than they have in months.

John Nelson (aka, Number2) would run the Truncate manually occasionally; but after a while that gets tedious. :) So he showed me how to see what is going to be truncated (query #1) and then how to make a Scheduled Job that runs daily, to actually do the Truncate of History tables.

Query #1: This particular query is only to look at what you have. It does nothing but show you results. Run this against all of your ConfigMgr sites with a CM_ database; and see if there is history you want to truncate. If so, you may want to then move on to running SQL #2 (below).


SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
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
INNER JOIN
    ArticleData AD on AD.ArticleName = T.Name
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.NAME LIKE '%[_]HIST'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    AND AD.ReplicationID not in (Select ID from ReplicationData where ReplicationPattern = 'global')
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    rowcounts desc

SQL #2: This will CREATE a job, with a daily schedule. Before you run it, change CM_FUN to be your CM_<your Site Code> ; and you may also want to change
@active_start_date=20140909
prior to running it, to whatever date you want the daily schedule to really start. Once created, presuming SQL Server Agent is running, on that SQL server, for the Databse of CM_<whatever you put in>, it'll truncate your history tables on the schedule defined.

Optional: After you've run the below, in your SQL Management Studio, Sql Server Agent, Jobs, if your right-click on the new job "ConfigMgr Truncate History Tables", you can select "Start Job at Step..." to have the job run RIGHT now; to confirm it works. Once it's done, you can re-run query #1 above and see that it's clean(er). Note that as machines report inventory, data will go into the history tables frequently. You may already have new rows after you just ran the Truncate job, but it should be much less than it was.

Optional: The next day, or weekly, or monthly...whatever schedule you have internally for checking up on your ConfigMgr infrastructure, every once in a while, run Query #1 above; and/or every once in a while, in SQL go to SQL Server Agent, Jobs, right-click on the Configmgr Truncate History Tables job, and select "View History", to see that the job was successful.


USE [msdb]
GO 
/****** Object:  Job [ConfigMgr Truncate History Tables]    Script Date: 9/8/2014 2:05:50 PM ******/ 
BEGIN TRANSACTION 
DECLARE @ReturnCode INT 
SELECT @ReturnCode = 0 
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/8/2014 2:05:51 PM ******/ 
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) 
BEGIN 
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
END 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ConfigMgr Truncate History Tables',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=0,
                @notify_level_netsend=0,
                @notify_level_page=0,
                @delete_level=0,
                @description=N'Truncate ConfigMgr database History tables',
                @category_name=N'[Uncategorized (Local)]',
                @owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
/****** Object:  Step [Truncate]    Script Date: 9/8/2014 2:05:51 PM ******/ 
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate',
                 @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_success_step_id=0,
                @on_fail_action=2,
                @on_fail_step_id=0,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem=N'TSQL',
                @command=N'USE [CM_FUN]
GO
DECLARE @SQL NVARCHAR(MAX) = N''
'' 
SELECT
  @SQL = @SQL+N''TRUNCATE TABLE dbo.''+TABLE_NAME+''; 
''  
FROM
  INFORMATION_SCHEMA.TABLES x
  INNER JOIN ArticleData A on x.TABLE_Name = A.ArticleName
WHERE
  x.TABLE_SCHEMA = ''dbo''
  AND x.TABLE_NAME LIKE ''%[_]HIST''
  AND A.ReplicationID not in (Select ID from ReplicationData where ReplicationPattern = ''global'')
ORDER BY
  x.TABLE_NAME exec sp_executesql @SQL
',
                @database_name=N'CM_FUN',
                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ConfigMgr Truncate History',
                @enabled=1,
                @freq_type=4,
                @freq_interval=1,
                @freq_subday_type=1,
                @freq_subday_interval=0,
                @freq_relative_interval=0,
                @freq_recurrence_factor=0,
                @active_start_date=20140908,
                @active_end_date=99991231,
                @active_start_time=231100,
                @active_end_time=235959,
                @schedule_uid=N'9936718a-af85-497b-ac0d-d47d91ce99d8'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO End
Save 

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:
GO

Created on 29 April 2019.