Understanding FEPDW database sizing
The FEPDW database may be larger than estimated prior to deploying FEP to all the clients in the environments and\or may be larger than estimated after full deployment if you used the FEP Capacity Planning worksheet for size estimation purposes.
For the scenario where the FEPDW is larger than expected prior to deploying FEP to all clients, where you may have deployed FEP to a test group, this is expected and by design. The FEPDW Database contains tables named dtFEP_AM_ComputerDeploymentAndProtectionStateFact_XX where XX is some integer value. These tables will be the largest contributors to the size of the FEPDW as they maintain the historical data of all the SCCM clients in the environment in regards to their FEP state. State includes items such as Engine Version, Signature Version, Write Time and so on. These items will have values for every system, not just the ones that have the FEP Client installed. This is how we maintain statistics for deployment of the FEP client.
For the scenario where the FEPDW is larger than expected after deploying to all clients, this could be for several reasons which I will step through below. When using the Capacity Planning Worksheet, the information is related to estimates on the size of records and the number of collections, along with the number of clients. We need to confirm each before we assume there is some other issue with the FEPDW Database.
- Number of collections. To determine the closest approximation of average collections, run the following SQL Query against the SCCM Database:
WITH MembershipsPerComputer AS
(select COUNT(*) InHowManyCollections from [CollectionMembers] where isClient = '1' group by machineid)
select AVG(M.InHowManyCollections) AS NumberOfCollectionsPerClientAvg
FROM MembershipsPerComputer AS M
2. Size of each record. To determine the size of each record stored in the dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables, run the following SQL Query against the FEPDW Database:
SET NOCOUNT
ON
CREATE TABLE #TBLSize
(Tblname
varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @DBname
varchar(80)
DECLARE @tablename
varchar(80)
SELECT @DBname
= DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database):
' + @@ServerName + ' / ' +
@DBName
PRINT
''
PRINT
'By Size
Descending'
DECLARE TblName_cursor
CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType
=
'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
#tblSize(Tblname,
TblRows,
TblReserved,
TblData,
TblIndex_Size,
TblUnused)
EXEC Sp_SpaceUsed @tablename
--
Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname
as Varchar(60)) 'Table',
CAST(TblRows
as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB',
TblReserved)) as int) 'Total Space (KB)',
CAST(TblData
as Varchar(14)) 'Data Space',
CAST(TblIndex_Size
as Varchar(14)) 'Index Space',
CAST(TblUnused
as Varchar(14))
'Unused
Space'
FROM #tblSize
Order by 'Total Space (KB)'
Desc
DROP TABLE #TblSize
3. The output above will have the Row Count column and Total Space (KB) column. We will use these to give us the approximate size of the each record. Add up the Total Space (KB) column for the
dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables, then add up the Row Count column for the dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables. Divide the total from the Total Space (KB) columns by the total from the Row Count column and this will give you the current average record size. The number should be somewhat close to the value from the FEP Capacity Planning worksheet
of 0.1499.
4. We now have the Average Collections number and the Average Record size for the dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables which can then be used in the FEP Capacity Planning worksheet to see if the estimated size of the worksheet is now closer to the actual size of the FEPDW Database. Put the value from step 1 above in the “Average number of collections to which each client
computer belongs:” cell. Then modify the “Computer deployment and protection state fact table” record size in kilobytes value to what was determined from step 3 above.
5. Once these are entered the value of the Total FEP Datawarehouse size should more closely match what the actual size of the FEP DW database is showing in SQL.
6. There are other factors, including the Retention Period which defaults to 12 months (365 days), however, this can be changed from any value of 3 to 12. See the following for details - https://technet.microsoft.com/en-us/library/gg710931.aspx. Please note that once this is changed, it is not effective immediately. The FEP DW maintenance task listed here - https://technet.microsoft.com/en-us/library/gg710933.aspx - runs once a day, but it does not initially remove the data older than the new value you have chosen. There is a 10 day layover period before the new grooming date takes effect. After the 11 day, you should see that the FEP DW Maintenance job has now dropped the dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables that have data older than the specified amount of time you have set for Retention. To confirm this, run the following SQL query against the FEPDW database and notate the End Time column. It will be within your specified Retention time:
select OBJECT_NAME(TableID),*
from dtAN_Infra_MaintenancePartition where OBJECT_NAME(TableID) =
'dtFEP_AM_ComputerDeploymentAndProtectionStateFact_SCHEMA'
7. If the End Time on the dtFEP_AM_ComputerDeploymentAndProtectionStateFact tables is outside of the Retention time that has been sent and it has been longer than 10 days since setting the new Retention time, then we need to verify the FEPDW Maintenance tasks are running. Check for errors regarding these Maintenance tasks in both the Event logs and the SQL Jobs View History option.
NOTE: If you change the retention period, FEP does not shrink the FEPDW database for you to recover space. You must use SQL to do this manually - https://technet.microsoft.com/en-us/library/ms189035.aspx