Hello, Reviewing the growth of the SCCM database I ran several queries: http://social.technet.microsoft.com/Forums/en-US/configmgrdocs/thread/556023d7-460f-468b-a8fc-9ec7298b02eb/ MSreplication_options 16 0 0 spt_fallback_db 0 0 0 spt_fallback_dev 0 0 0 spt_fallback_usg 0 0 0 spt_monitor 16 0 0 spt_values 336 136 0 and : http://www.sqlteam.com/downloads/BigTables.sql CI_ConfigurationItems 35577 300472 KB 281424 KB 13264 KB 5784 KB LU_SoftwareList 372658 179352 KB 72208 KB 106264 KB 880 KB SoftwareInventory 1311491 132552 KB 94424 KB 37272 KB 856 KB LU_SoftwareHash 282476 96832 KB 59480 KB 36504 KB 848 KB CI_SDMPackages 66285 82416 KB 53120 KB 28224 KB 1072 KB INSTALLED_EXECUTABLE_DATA 191867 69984 KB 59296 KB 10000 KB 688 KB SOFTWARE_SHORTCUT_DATA 116652 58544 KB 57792 KB 360 KB 392 KB LU_SoftwareCode 187442 50808 KB 27112 KB 23504 KB 192 KB CI_SDMPackageRelations 796619 48400 KB 34272 KB 13968 KB 160 KB RecentlyUsedApps_DATA 61074 39568 KB 26560 KB 12688 KB 320 KB SoftwareFile 76644 33000 KB 12416 KB 18200 KB 2384 KB StatusMessageAttributes 112613 24072 KB 11704 KB 10800 KB 1568 KB INSTALLED_SOFTWARE_DATA 21929 20432 KB 14464 KB 5504 KB 464 KB Policy 115179 18712 KB 18328 KB 384 KB 0 KB CI_ContentFiles 40842 18608 KB 13712 KB 2712 KB 2184 KB StatusMessages 87269 18416 KB 11712 KB 5808 KB 896 KB LU_MSProd 45982 17536 KB 10888 KB 6464 KB 184 KB Services_DATA 70984 17072 KB 16824 KB 216 KB 32 KB TableChangeNotifications 0 16768 KB 16440 KB 64 KB 264 KB PNP_DEVICE_DRIVER_DATA 74926 16304 KB 15880 KB 192 KB 232 KB Add_Remove_Programs_DATA 73967 15728 KB 15032 KB 184 KB 512 KB Update_DetectionStatus 81227 15472 KB 10192 KB 4616 KB 664 KB SoftwareFilePath 51299 12992 KB 7072 KB 5456 KB 464 KB System_Devices_DATA 36920 11040 KB 10632 KB 152 KB 256 KB SW_LICENSING_PRODUCT_HIST 15450 10920 KB 10624 KB 168 KB 128 KB I don't see why my databse is 63 Gb ... and only 2 GB Free space where are the other "space" eater? I see only 1.3 Gb for these tables... what are the other component using space in the DB? It seems a table is missing ? I have only 650 Client on this server? on the big server with 5500 desktops I have a smaller size for the DB opnly 60 GB ??? what is wrong? It seems I am missing the HW tables???? /************************************************************************************** * * BigTables.sql * Bill Graziano (SQLTeam.com) * graz@sqlteam.com * v1.1 * **************************************************************************************/ declare @id int declare @type character(2) declare @pages int declare @dbname sysname declare @dbsize dec(15,0) declare @bytesperpage dec(15,0) declare @pagesperMB dec(15,0) create table #spt_space ( objid int null, rows int null, reserved dec(15) null, data dec(15) null, indexp dec(15) null, unused dec(15) null ) set nocount on -- Create a cursor to loop through the user tables declare c_tables cursor for select id from sysobjects where xtype = 'U' open c_tables fetch next from c_tables into @id while @@fetch_status = 0 begin /* Code from sp_spaceused */ insert into #spt_space (objid, reserved) select objid = @id, sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages where objid = @id /* index: sum(used) where indid in (0, 1, 255) - data */ update #spt_space set indexp = (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) - data where objid = @id /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) where objid = @id update #spt_space set rows = i.rows from sysindexes i where i.indid < 2 and i.id = @id and objid = @id fetch next from c_tables into @id end select Top 25 Table_Name = (select left(name,25) from sysobjects where id = objid), rows = convert(char(11), rows), reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' order by reserved desc drop table #spt_space close c_tables deallocate c_tables I even changed the 25 to 100 then 1000 to capture all tables but I never reached 2 Gb and no hardware table listed !!! What is (are) the name of the Hardware tables, hardware Inventory, etc...? I saw HW_TEMP_SQL only 272 KB, HW_TEMP 80 KB but there should anothertable ... using the whole space !!! Thanks, Dom System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

the tables *_DATA are the Hardware table but why their size is so small vs the database size??? Thanks, DOmSystem Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

I did another query more general on the SMS DB and I see a table CI_SDMPackages using 49,156,684 KB !!!! then Policy 6,021,904 KB so with these items I have already 55 GB +... For comparison on the Central Site the same table CI_SDMPackages is using 2,5 Gb!!! and Policy 3,3 Gb !!! and on the Desktop site with 5,000 + Clients table CI_SDMPackages is using 47 Gb!!! and Policy 3,3 Gb !!! What are they used for ? I am reviewing these tables with this http://blogcastrepository.com/blogs/brian_tucker/archive/2009/06/18/how-to-resolve-site-replication-issues-with-sccm-2007-sp1-r2.aspx... It seems the issue in on two child sites already !!!! (: As one of the Child site is now on its won no more parent/child relationship it seems I will have to review the "Central Site Code' as no item are returned from the Select * from CI_SDMPackages where SourceSite 'Central_Site_Code'... On both Child Site I have the 12 site code "Null" and 0 (zero) Central SIte Code on the Server having the big database.... the one which has been detached from the Central Site... What could be done next? On the Child site still Child of the Central Site I could do the process from Brian Tucker ... on Thanks, DOm System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

I did another query more general on the SMS DB and I see a table CI_SDMPackages using 49,156,684 KB !!!! then Policy 6,021,904 KB so with these items I have already 55 GB +... For comparison on the Central Site the same table CI_SDMPackages is using 2,5 Gb!!! and Policy 3,3 Gb !!! and on the Desktop site with 5,000 + Clients table CI_SDMPackages is using 47 Gb!!! and Policy 3,3 Gb !!! What are they used for ? Thanks, DOm System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

They are used for Software Updates.http://www.enhansoft.com/

Hello Garth, We do not use Software Update anymore in our enviroment we have gone back to WSUS after six months under SCCM. So do you think it is remaining useless records from this six months? Also we have disconnected the link Parent-Child1, Parent-Child2 is still active but not used... Do we have if necessary an uninstallation process to clean-up this? The sql from Brian Tucker released only 1Gb on Child2 and 1.5 Gb on child1 for the table CI_SDMPackages . So the DB is still over 60 Gb.... Child1 Before 49,156,584 Child1 After 47,608,032 Gain 1,548,552 Child2 Before 47,084,984 Child2 After 46,059,472 Gain 1,025,512 Should this table remains at this level 47-49 Gb? Thanks, DomSystem Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

I hope that you know that editing the db is completely unsupported. Make sure that all you maintenance tasks are enabled.http://www.enhansoft.com/

Folks get varying measures of success in reducing DB size http://social.technet.microsoft.com/Forums/en/configmgrsum/thread/a0683695-3224-40b2-b6b0-8a6a4ae57353 Is the table heavily fragmented?

it seems to be a loop as this does not get any more space either... Thanks, DomSystem Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

Things sound unusual, I think it best you open a CSS case, and get them to investigate this further for you.

Hello, Any process to Uninstall Software Update points, and remove any links with this module of Software Updates as we do not use it anymore? Thanks, DomSystem Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

You can remove the SUP role from your site server. As for the data in the database, there is a maintenance task to clean up Configuration Managment data: http://technet.microsoft.com/en-us/library/bb693964.aspx