Our EventSentry database is growing very quickly and we would like to know which tables (which features) are using up the majority of the disk space.
Please see the instructions below to see how much space a feature is using:
Microsoft SQL Server:
Run the following command in SQL Query Analyzer to see how many rows and how much data is being used by the tables in the EventSentry database:
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
A better way to see a sorted list of tables, including row count, table and index size, is the following statement (see Additional Links below for a link to the file):
CREATE TABLE #TempTableSize
(
name varchar(255),
rows int,
reserved varchar(25),
data varchar(25),
index_size varchar(25),
unused varchar(25)
)
DECLARE tables_cursor CURSOR
FOR
SELECT distinct name FROM sysobjects WHERE type = 'U' and name like 'ES%'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO #TempTableSize
EXEC sp_spaceused @tablename
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
SELECT
name,
CAST(rows AS int) AS rows,
CAST(RTRIM(SUBSTRING(reserved, 1, CHARINDEX('KB', reserved) -1)) AS int) AS reserved,
CAST(RTRIM(SUBSTRING(data, 1, CHARINDEX('KB', data) -1)) AS int) AS data,
CAST(RTRIM(SUBSTRING(index_size, 1, CHARINDEX('KB', index_size) -1)) AS int) AS index_size,
CAST(RTRIM(SUBSTRING(unused,1, CHARINDEX('KB', unused) -1)) AS int) AS unused
INTO #TempTableSize2
FROM #TempTableSize
DROP TABLE #TempTableSize
SELECT * FROM #TempTableSize2 ORDER BY reserved DESC
DROP TABLE #TempTableSize2
Tables that will usually accumulate a lot of data are:
- ESEventlogMain
- ESDiskspace
- ESPSTracking
- ESPerformance
- ESEventlogData
MySQL:
Download the MySQL Administrator (see additional links below) and connect to the MySQL server. Once connected, click on "Catalogs" and select the "EventSentry" database. You will see size of each table and the number of rows.
