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
