Grooming EdgeSight databases (Advanced)

Due to the large amount of data collected by EdgeSight, it is recommended that good housekeeping should be completed by the EdgeSight administrator for EdgeSight to provide optimal results.

Similar problems can also be noticed if there is not enough disk space on the drive or other problems with the database. As a result of any of these problems, grooming errors might occur and those will also be displayed in the console.

In EdgeSight the primary database management mechanism is called grooming and is defined as the process of removing older data from a database at regular intervals to make room for new data.

How to do it...

There are eight data files in the EdgeSight database; the default location of these files is at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\.

1. We first need to find the data files that are larger in size than other data files. We can run the following query to extract this information:

   SELECT Name, (size*8)/1024 Size
   FROM sys.master_files
   WHERE DB_NAME(database_id) = 'Edgesight'
   

   The following table is the output I received after executing the query. Your output (size) will be different:

   Name	Size
   Edgesight	2,500
   Edgesight_log	45,052
   Edgesight_FG1_Data	12,820
   Edgesight_FG2_Data	25,350
   Edgesight_FG3_Data	7,322
   Edgesight_FG4_Data	46,623
   Edgesight_FG5_Data	8,250
   Edgesight_FG6_Data	245,020
   Edgesight_FG7_Data	12,500

   From the previous results we can see that in our case the database Edgesight_FG6_Data is the largest in size. The size is in MBs.

2. The next step is to determine the names of the tables that are hosted by that particular file group. We can determine those names by running the following query:

   SELECT  DISTINCT object_name(sys.sysindexes.id) as 'Table Name' ,sys.filegroups.name as 'File Group Name'
   FROM sys.sysindexes, sys.filegroups
   WHERE objectproperty(sys.sysindexes.id,'IsUserTable') = 1
   AND sys.filegroups.data_space_id = sys.sysindexes.groupid
   ORDER BY sys.filegroups.name
   

   The output from this query in my case was as follows:

   

3. The next step now would be to find out the number of rows in each table. Run the following query in SQL Server Management Studio to find the number of rows in both the core_net_trans and core_net_stat tables:

   SELECT sysobjects.Name, sysindexes.Rows
   FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
   WHERE sysindexes.IndId < 2 AND sysobjects.Name IN('core_net_stat', 'core_net_trans')
   

   The output from this query in my case was as follows:

   Name	Rows
   core_net_stat	250475223
   core_net_trans	2457843

   These results clearly tell us that the table named core_net_trans has more rows than core_net_stat and hence core_net_trans would also be much larger in size compared to core_net_stat.

4. The next step would be to find out the number of records that for some reason could not be deleted after the number of Groom Days has passed. The default Groom Days value for both the core_net_trans and core_net_stat tables is 10 days. To verify the Groom Days you can navigate to Configure | Server Configuration | Data Maintenance | Grooming.

   

5. We can use the following query to determine the number of records (if any) that could not be deleted:

   SELECT COUNT(*) FROM CORE_NET_TRANS
   WHERE dtperiod < GETUTCDATE() – 10
   

   Note

   If rows are returned from the preceding query that were older than 11 days, then that would mean that grooming failed. If no rows are returned older than the 10 days, further investigation needs to be performed and you might start from taking a look at what, and how much, data is stored inside the particular table.

6. Now we can run this query to delete rows by increments of 100,000:

   Declare @row int; cf1 declare @date datetime; cf1 set @date = GETUTCDATE() - 10
   Set @row = (select COUNT(*) from core_net_trans where dtperiod < @date);
   While @row <> 0
   Begin
   Delete top(100000) from core_net_trans where dtperiod < @date
   Set @row = (select COUNT(*) from core_net_trans where dtperiod < @date);
   End;
   

7. We also need to reclaim the disk space after removing the unwanted rows. To do that, open SQL Server Management Studio and right-click on your EdgeSight database name. Here I have used EdgeSight as the name; hence, click on EdgeSight | Tasks | Shrink | Files.

   

8. Select FG6 as the Filegroup value and click on OK.

   

There's more...

You can also limit data uploads from the agents to the EdgeSight Server database by deselecting the type of performance data you are not interested in gathering. To configure these settings, navigate to Configure | Server Configuration | Data Maintenance | Upload Configuration.

This also optimizes the EdgeSight Server performance.