cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

How do I determine the right amount of Storage for my NTA 4.0 Flow Storage Database, based on the my NTA 3.11.0 usage?

I tried the script mentioned in the link SolarWinds Knowledge Base :: NTA 4.0 Installation: Frequently Asked Questions but the MS-SQL 2008R2 Management Studio shows an error " Invalid object name 'NetFlowGlobalSettings' ". I browse on the NetPerfMon database  finding a tables called dbo.NetFlowGlobalSettings. What is the correct script to know the right amount of Storage for my NTA 4.0 Flow Storage Database based on the my NTA 3.11.0 usage?

This is the script:

DECLARE @FSDB_row_size INT;

DECLARE @detailed_row_count BIGINT;

DECLARE @retention_period   BIGINT;

DECLARE @AmountOfDetailTablesPerNode AS INT

SET @FSDB_row_size = 126;

---------------------------------------------------

-- ** Select settings from NTA Global settings **

SELECT @AmountOfDetailTablesPerNode = CONVERT(INT, Value)

FROM NetFlowGlobalSettings

WHERE KeyName ='RetainUncompressedDataIn15MinuteIncrements'

SELECT @retention_period = value*24*60*60 FROM dbo.NetFlowGlobalSettings WHERE KeyName='RetainCompressedDataInDays'

-- ** Get all detail tables **

SELECT * INTO #AllDetailTables FROM

(

    SELECT SUBSTRING(name,15,PATINDEX('%[0-9][_]%', name)-14) AS NodeID,

           RIGHT(name, LEN(name) - PATINDEX('%[0-9][_]%', name)-1) as Interval,

           name, id

    FROM dbo.sysobjects

    WHERE name LIKE 'NetFlowDetail[_][0-9]%' AND OBJECTPROPERTY(id, N'IsUserTable') = 1

) AS A

-- ** Get only first [amount of 15 minutes intervals] detail tables per each node **

DECLARE @script as NVARCHAR(max)

SET @script ='

SELECT * INTO ##DetailTables FROM #AllDetailTables a

WHERE name IN

(

    SELECT TOP ' + CONVERT(nvarchar, @AmountOfDetailTablesPerNode) + ' name FROM #AllDetailTables b

    WHERE b.NodeID=a.NodeID

    ORDER BY Interval

)

ORDER BY name

'

EXECUTE sp_executesql @script

-- ** Get sum of all rows in given detail tables **

SELECT @detailed_row_count = SUM(row_count)

FROM sys.dm_db_partition_stats stats

JOIN ##DetailTables dt ON (stats.object_id = dt.id)

-- ** Calculate target size based on amount of rows in detail tables **

SELECT ((@detailed_row_count / (@AmountOfDetailTablesPerNode*15*60)) * @FSDB_row_size*@retention_period) / 1024 / 1024 / 1024 AS[Required_FSDB_Disk_Size_GB];

DROP TABLE #AllDetailTables

DROP TABLE ##DetailTables

Kind Regards,

Faridt

0 Kudos
0 Replies