Identifying the reason for Database growth

PROBLEM:

How do I determine why my database has been growing exponentially?

SOLUTION:

Use the (KaseyaURL)/inc/perftest.asp page as the basis for investigation.

1). Take a look at the database size and free space. You can take a quick look and see what the database size is, the size of the log file, and the amount of space that you can get back. Many times, you can get a lot of space back simply by running a database shrink.

2). Going further, you can look at the chart for the 'Top 25 tables of the database'. Focus on the first top 10 and make a note of the table name with the largest 'Allocated in M(megabytes)' and 'Row Count (approx)'.

Look at the table name and you can get an idea of what function that the table is associated with.

Example: If you see the 'scriptlog' table at the top of the list, this indicates that there are either a lot of scripts running that is logging data or that the logs are being kept for a long time.

If you see the monitoralarm table at the top, this indicates that there are too many alarms being generated.

Disregard the auditrsltApps table as this is generally the largest table in every customers database.

3). Next, look at the 'Number of tables per log type. Indicator for failed archiving' and compare it to the number of days that are specified in the Log History Setting chart next to it. If you see a huge disparity, then there is most likely an issue with archiving on your system.

4). Finally, at the bottom of the page, if you see agents flooding the database, you will need to look into those agents as they are most likely filling up the ntEventLog tables with data on a daily basis and causing your tables to grow.

Here is a query to list the tables and the number of rows for some of the important tables.

SELECT
object_schema_name(t.object_id) + '.' + t.name as tableName
, SUM(stat.row_count) as numberOfRows
FROM sys.dm_db_partition_stats stat
INNER JOIN sys.tables t
ON stat.object_id=t.object_id
WHERE
stat.index_id in (0,1)
AND t.type='U'
AND object_schema_name(t.object_id) + '.' + t.name in
(
'dbo.partnerPartitions'
, 'dbo.partnerUser'
, 'dbo.funcList'
, 'dbo.funcAccess'
, 'dbo.machNameTab'
, 'dbo.users'
, 'dbo.userIpInfo'
, 'dbo.machGroup'
, 'dbo.scriptIdTab'
, 'dbo.appSession'
, 'dbo.administrators'
, 'dbo.adminGroup'
, 'dbo.userLogon'
, 'kasadmin.org'
, 'kasadmin.organization'
, 'kasadmin.orgDepartment'
, 'kasadmin.orgStaff'
, 'kasadmin.orgNetwork'
, 'kasadmin.orgNetworkAgent'
, 'inventory.asset'
, 'inventory.hwDevice'
, 'inventory.swApps'
, 'dbo.alertAction'
, 'dbo.monitorAlarm'
, 'dbo.wsNewAlarm'
, 'dbo.wsNewTicket'
)
GROUP BY
object_schema_name(t.object_id) + '.' + t.name
ORDER BY tableName

Browse this section