Hello,
Large amounts of data are usually a sign that polling interval in the probes are too low, and capturing too much data. You mentioned you only have the system 1 moth so at this point data retention should yet be a factor.
To find out what probes are capturing so much data, so you can change the polling interval you can run the below query:
Query to determine which probes/sources are generating the most QOS
-- (Same results as the data_engine Status button but adjusted for bigint)
create table #t (id bigint, name varchar(max), rname varchar
(max), hname varchar(max), dname varchar(max), bname
varchar(max),
rrows bigint, hrows int, drows int, brows int, rsize
int, hsize int, dsize int, bsize int)
insert #t (name,rname,rrows,id, hname, dname, bname)
SELECT sqd.name, object_name(i.object_id), sum(p.rows)
as rowCnt, sqd.qos_def_id,
replace(object_name(i.object_id),'RN','HN'), replace
(object_name(i.object_id),'RN','DN'), replace(object_name
(i.object_id),'RN','BN')
FROM
sys.indexes i INNER JOIN sys.partitions p ON i.object_id =
p.object_id AND i.index_id = p.index_id
INNER JOIN s_qos_definition sqd on
sqd.qos_def_id = cast(replace(object_name
(i.object_id),'RN_QOS_DATA_','') AS int)
WHERE object_name(i.object_id) like 'RN_QOS_DATA_%'
AND i.index_id <= 1
GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id
To see the table results, then run the SELECT portion:
SELECT sqd.name, object_name(i.object_id), sum(p.rows)
as rowCnt, sqd.qos_def_id,
replace(object_name(i.object_id),'RN','HN'), replace
(object_name(i.object_id),'RN','DN'), replace(object_name
(i.object_id),'RN','BN')
FROM
sys.indexes i INNER JOIN sys.partitions p ON i.object_id =
p.object_id AND i.index_id = p.index_id
INNER JOIN s_qos_definition sqd on
sqd.qos_def_id = cast(replace(object_name
(i.object_id),'RN_QOS_DATA_','') AS int)
WHERE object_name(i.object_id) like 'RN_QOS_DATA_%'
AND i.index_id <= 1
GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id
ORDER BY rowCnt DESC
Please note "Maintenance in not recommended for large tables (over 10 GB)". So to delete the data past a period of time you can use the query below:
Query to purge data based on the age of the data
--Purge of data by date. See date (as per sampletime below)
--Here is the Script to check WHAT you'll be deleting:
declare table_id_cursor cursor for select table_id from s_qos_data
declare @sql varchar(1000)
declare @r_table varchar(64)
declare @h_table varchar(64)
declare @table_id int
OPEN table_id_cursor
FETCH NEXT FROM table_id_cursor into @table_id
WHILE @@FETCH_STATUS = 0
BEGIN
set @r_table = (select r_table from S_QOS_DATA where table_id = @table_id)
set @h_table = (select h_table from S_QOS_DATA where table_id = @table_id)
set @sql = 'select COUNT(*) from ' + @r_table + ' where table_id = ' + CAST(@table_id AS varchar(10)) + ' and sampletime < ''2016-05-30 00:00:00'''
EXECUTE(@sql)
set @sql = 'select COUNT(*) from ' + @h_table + ' where table_id = ' + CAST(@table_id AS varchar(10)) + ' and sampletime < ''2016-05-30 00:00:00'''
EXECUTE(@sql)
FETCH NEXT FROM table_id_cursor into @table_id
END
CLOSE table_id_cursor
DEALLOCATE table_id_cursor
-- Here is the Script to actually DELETE the data (BACK UP YOUR DB FIRST!!!)
-- Note the delete statements are currently limited to the top 5000 rows so the operation can complete. This value can be adjusted.
-- You can use the TOP clause to limit the number of rows that are deleted in a DELETE statement.
-- When a TOP (n) clause is used with DELETE and no where clause, the delete operation is performed on a random selection of n number of rows.
-- Use a SELECT statement and data in the wehere clause to verify deletion
declare table_id_cursor cursor for select table_id from s_qos_data
declare @sql varchar(1000)
declare @r_table varchar(64)
declare @h_table varchar(64)
declare @table_id int
OPEN table_id_cursor
FETCH NEXT FROM table_id_cursor into @table_id
WHILE @@FETCH_STATUS = 0
BEGIN
set @r_table = (select r_table from S_QOS_DATA where table_id = @table_id)
set @h_table = (select h_table from S_QOS_DATA where table_id = @table_id)
set @sql = 'delete top (1000) from ' + @r_table + ' where table_id = ' + CAST(@table_id AS varchar(10)) + ' and sampletime < ''2016-05-30 00:00:00'''
EXECUTE(@sql)
set @sql = 'delete top (1000) from ' + @h_table + ' where table_id = ' + CAST(@table_id AS varchar(10)) + ' and sampletime < ''2016-05-30 00:00:00'''
EXECUTE(@sql)
FETCH NEXT FROM table_id_cursor into @table_id
END
CLOSE table_id_cursor
DEALLOCATE table_id_cursor
Please note any changes to the DB are unsupported, and I would recommend you open a support case to investigate.
Hope this helps.
Thanks