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
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 @[email protected], @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_NAMEexec 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 @[email protected], @name=N'ConfigMgr Truncate Hsitory', @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 QuitWithRollbackCOMMIT TRANSACTIONGOTO End Save QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:GO
- Created on .