PROBLEM:
My database is growing. How do I begin troubleshooting this?
SOLUTION:
The reasons for database growth vary from customer to customer. However, the biggest reason for why the database has grown so large is due to server configuration.
How long do you have your log history set as? Log History is located under the Agent tab > Log History. The longer you choose to keep your log history, the larger your database will be. A user who chooses to keep only 30 days of logs will have a significantly smaller database compared to those users who decide to keep 60 days of logs. We at support recommend that logs be kept no longer than 30 days.
Here is a query to help you determine how big your database is and how much space you may possibly get back from a database shrink. Many times, a database can be shrunk simply by performing a database shrink.
SELECT DB_NAME() AS 'DB Name',
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
type_desc AS 'Type', physical_name AS 'Physical Name',
state_desc AS 'State'
FROM sys.database_files
UNION
SELECT 'DB Total Size', ' ', SUM(size/128.0),
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0),
' ', ' ', ' '
FROM sys.database_files
ORDER BY DB_NAME() desc
Here is a query to look at all the largest tables in your Kaseya database.
SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],
CAST(CAST(reserved / 1000000.0 AS DECIMAL(10,6)) * 8192 AS DECIMAL(10,1)) AS 'Allocated (in M)',
CAST(CAST(dpages / 1000000.0 AS DECIMAL(10,6)) * 8192 AS DECIMAL(10,1)) AS 'Used (in M)',
CAST(CAST((reserved - dpages) / 1000000.0 AS DECIMAL(10,6)) * 8192 AS DECIMAL(10,1)) AS 'Unused (in M)',
rowcnt AS 'Row Count (approx.)'
FROM sysindexes
WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE 'sys%' AND OBJECT_NAME(id) NOT LIKE 'dt%'
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)
This query is ideal in determining specifically what your largest tables are and where you need to edit your configurations.
For example, if you see that event log tables are the largest tables in your database, you will need to evaluate all the event logs you are collecting and determine which machines you would like to keep event logs on and which machines you would like stop collecting events on.
If you have any questions about why certain tables are large in your database, please submit a ticket for the respective module that the table is referencing.
PLEASE DO NOT DELETE ANY DATA FROM THE DATABASE WITHOUT FIRST CONSULTING SUPPORT AS DELETING THE WRONG DATA WITHOUT CONSULTATION COULD BE DETRIMENTAL TO YOUR SYSTEM.