Kaseya Community

Database size - cleanup DB and prevent large DB

This question is not answered

Hi,

We are using MSSQL Express, so the Database can be 10GB.

From time to time the Database limit is reached and i have to clear some tables (or old records).

But lately the limit is reached very often

The 'scriptlog' table is very large, so i delete all records older than 1 month.

Can i setup Kaseya so this tble does not grow older than 1 month?

And i have a lot of tables created for each day.

Example:

  • monitorCounterLog20160519
  • ntEventLog20160519
  • ErrorLog20160519
  • monitorProcessLog20160519

The above tables are made for each day, and don't seem to be deleted/flushed automaticly.

Is there a setting so i can setup a max ?

The ErrorLogYYYYMMDD can be about 250MB a Day, so that grows our DB very fast.

Any thoughts?

All Replies
  • This TechJam has some good tips on things do to to keep it under control.

    www.youtube.com/watch

    Off the top of my head I would look at log history in the agent module.  Also keep in mind since like 6.3 or something like that you do not need to retain the event logs for monitoring to alert so if you don't have a business case to keep them I would say clear them all.  The video goes into both of these things.

    Also use the /inc/perftest.asp site to review how things look, if you don't have the latest one I would get it.  You can get it here-

    helpdesk.kaseya.com/.../97374758-How-To-Get-the-Latest-PerfTest-asp-Page

    I had a case were one of the accounts that does the cleanup was missing some permissions so the auto clean up wasn't happening.  If that seems to be the case a support ticket might be your best option.

  • Hi  

    As  makes great points, I would also like to highlight this DB maintenance plan our PSE and Support Team has put together:

    https://helpdesk.kaseya.com/entries/98809758-How-To-Database-Maintenance

    The steps can also be automated to recur which is what I believe you are looking for.

    If you do not want those monitorxxxxxxLog and ntEventLogxxxxx for 30 days, you can change the retention period;

  • Hello Mooya,
    In addition to maintaining table size to a limited number of record (methods others have already answered)
    you will probably have to keep "shrinking" the database periodically so that space from deleted tables is reclaimed.

    I rarely if ever shrink my DBs (but I have the Enterprise Version of SQL) and it is mentioned everywhere that fragmentation gets really bad when you do this.
    People seems that do not recommend using Automatic Shrink so, I have "assembled" few pieces together to do it (periodically).

    Index Rebuild / Reorganize (probably needed after a DB Shrink)
    This was taken from the excellent SQL Server maintenance scripts here:

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    And I have added a script to check if the database size is greater than 5GB (but you can change that)
    in which case it runs both a DBCC Shrink Database and right after a Index Reorganize.

    You could run this using either a Kaseya Procedure (periodically) or a Windows Task Scheduler on your SQL Server Box
    (because Express does not come with Agent Jobs) executing the sql script using
    sqlcmd -S <ComputerName>\<InstanceName> -U youruser -P yourpassword -i thesqlscript.sql

    If you have plenty of truncate tables / create - drop tables this may come handy.

    KeepSQLDB_Small.sql

    I would still create an alert in Kaseya and monitor my Database Size though.

    You could modify this part and change > 5000 (shrink DB > 5MB) to another value.
    You can also change the DBCC SHRINKDATABASE  options if 5% of free space should be changed.

    WHERE x.total_size_data > 5000;
    OPEN CHECK_DB_SIZE
    FETCH NEXT FROM CHECK_DB_SIZE INTO @dname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @dname;
        DBCC SHRINKDATABASE (@dname, 5);
        EXECUTE dbo.IndexOptimize @Databases = @dname, @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30;
        FETCH NEXT FROM CHECK_DB_SIZE INTO @dname;
    END
    CLOSE CHECK_DB_SIZE
    DEALLOCATE CHECK_DB_SIZE
    GO

  • thanks everyone.

    sorry for the late reaction.

    i'll try the above suggestions.