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:

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.

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:

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:

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.

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.

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

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;

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.

Select FG6 as the Filegroup value and click on OK.