Kaseya Community

Database cleanup

  • Hello,

    Our database is growing and growing. 42GB with 550 Servers. Is there a way to cleanup the database?

  • In my experience, this is most commonly caused by excessive log retention. The pages I would recommend checking out are [Agent > Log History] and [Agent > Event Log Settings].

    You can also check the database directly to see which tables are using the most space:

    SELECT

    t.NAME AS TableName,

    i.name AS indexName,

    SUM(p.rows) AS RowCounts,

    SUM(a.total_pages) AS TotalPages,

    SUM(a.used_pages) AS UsedPages,

    SUM(a.data_pages) AS DataPages,

    (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,

    (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,

    (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB

    FROM

    sys.tables t

    INNER JOIN  

    sys.indexes i ON t.OBJECT_ID = i.object_id

    INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

    INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

    WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

    GROUP BY

    t.NAME, i.object_id, i.index_id, i.name

    ORDER BY

    OBJECT_NAME(i.object_id)

  • Hello SMason,

    All eyes are pointing to the logging. Any way to force a cleanup?

    BTW thanx for the query!



    [edited by: Sander Rozemuller at 12:41 AM (GMT -7) on Jun 13, 2013] .
  • Hi Guys!

    If your logs are growing take a look to the DBCC SHRINK way...

    msdn.microsoft.com/.../ms189493.aspx

    It's works like a charms in SQL Server since 2005 ...

    The file name should be found in the SQL database properties in the file section

    USE ksubcribers;

    GO

    DBCC SHRINKFILE (ksubscribers_dat_log, 1);

    GO

  • It wouldnt just be the log file that would need shrinking.

    Once these log entries have hit the tables in the database

    NTEventLog<date>

    monitorCounterLog<date>

    errorLog<date>

    These need to be dropped and the command to use afterewards would be:

    dbcc shrinkdatabase('ksubscribers')

    This will compact both MDF and LDF files

    Shrinking just the Log files will not do much help in reducing the actual size of the database unless you can confirm that the bulk of the size is in the LDF file.

  • At one point our ksubscribers DB was over 70GB and all I had to do is lower the log retention, create monthly reports for any information we wanted to keep for longer than 30days and run the Kaseya Maintenance processes located in the VSA by using the Backup and Defrag database options.

    I now have a  28GB database and it will become even less after I have fined tuned my Eventlog monitoring.



    typo
    [edited by: HardKnoX at 3:22 PM (GMT -8) on Dec 5, 2013]
  • I had a similar experience. We went from 150GB down to 37GB solely from being smarter with log retention.

  • The script log was the reason for my DB size to grow.

    Unchecking the 'Enable logging of successful child script execution in agent procedure log' on the System - Configure page did the job for me.

  • on which DB are you performing the SELECT

    t.NAME AS TableName,

    i.name AS indexName,

    errmsg cannot connect