How much space did that save you?
From: [email protected] [mailto:[email protected]] On Behalf Of Todd Hemsell Sent: Thursday, August 13, 2015 1:53 PM To: [email protected] Subject: Re: [mssms] Delete old HINV SELECT 'TRUNCATE TABLE ' + 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 WHERE t.NAME NOT LIKE 'dt%' AND t.NAME LIKE '%[_]HIST' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY rowcounts desc On Thu, Aug 13, 2015 at 1:26 PM, Marcum, John <[email protected]<mailto:[email protected]>> wrote: So you literally just ran truncate table table_name_here ?????? From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Todd Hemsell Sent: Thursday, August 13, 2015 1:19 PM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] Delete old HINV I came up with that also, but I think it has escape characters in it '' I just took the list of tables and truncated the big ones. Thanks for verifying it is ok to truncate. On Thu, Aug 13, 2015 at 1:00 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: I'm "pretty sure" it's just the stuff inside the @Command in the 2nd section: USE [CM_FUN] GO DECLARE @SQL NVARCHAR(MAX) = N'' '' SELECT @SQL = @SQL+N''TRUNCATE TABLE dbo.''+TABLE_NAME+''; '' FROM INFORMATION_SCHEMA.TABLES x WHERE x.TABLE_SCHEMA = ''dbo'' AND x.TABLE_NAME LIKE ''%[_]HIST'' ORDER BY x.TABLE_NAME exec sp_executesql @SQL but... not really sure. :( of course, change cm_fun to be your database. On Thursday, August 13, 2015 11:43 AM, Todd Hemsell <[email protected]<mailto:[email protected]>> wrote: Awesome! Can you possibly send me just the SQL to truncate the tables? SQL Studio does not like the entire script to create the job. On Wed, Aug 12, 2015 at 2:10 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: How about all your history data? http://mnscug.org/blogs/sherry-kissinger/357-configmgr-2012-truncate-history-tables We have that running daily to clear the history. No one has ever asked us for history data. On Wednesday, August 12, 2015 2:06 PM, Todd Hemsell <[email protected]<mailto:[email protected]>> wrote: Anyone know how to either manually kick off the main task to del aged inv hist or know how to manually delete aged inventory? I have changed the date/time on the task to try and get it to kick off and it does not seem to have any affect. I had it misconfigured and filled my DB up. I urgently need to del some rows to free up some space. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
