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.
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