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.

Article ID: 76
Category: Database
Updated: 2006-07-11

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.