Excessive Kaseya Database Size

For years, we had set Kaseya to maintain 30 days of log files for workstations and servers that we manage.  However, a matter arose that made it really attractive to have access to a larger amount of historical data as what we wanted to double check was well beyond 30 days out.  There's nothing that could be done in that case, but can this headache be eliminated moving forward?  Kaseya support said that there would be no ill effects by upping this to 365 days or more, it would just cause the ksubscribers database to grow.  And since our database server was barely breaking a sweat, we made the change.

Fast forward about a year, and our previously 35GB ksubscribers database had ballooned up to well over 400GB, and everything involving Kaseya was dog slow.  The breaking point came when I could no longer get a reapply schema operation to complete.  It would fail at a different point each time, but not too far off from each other failure.  Since we were a couple revisions of Kaseya behind, and a new version was due to come out soon, I figured it was time to address this. Dropping the log retention back to 30 days did nothing, so I opened a ticket. 

The support tech found that the reapply schema failure was because the operation was timing out before completion due to the size of the database.  He was able to increase the timer so that it would finish, and then reset it back to it's original value.  He did not tell me what the value was or where I could find it and noted that leaving it too high would ultimately mask serious issues later on.  

Once we had a reapply schema completed, I was left with these steps to get the database size back under control.  We were told to only do this during a maintenance window, but since steps 2 and 3 took several days each, and everything ran fine while it was running, we couldn't do so.  Nobody can have a maintenance window on their main business app for a week or more.

1. Stop IIS and the Kaseya services.
2. In SQL Management Studio, run the following:
• DBCC SHRINKDATABASE('KSUBSCRIBERS')
3. Reindex / Rebuild the indexes.  We were provided with a stored procedure to do this, I'll have to dig it up but a Kaseya support tech should have it.
• exec [dbo].[sp_rebuildindexes]
4. Update the DB statistics.  Again, we were provided with a stored procedure to do this. 
• EXEC [dbo].[sp_update_stats]
• @dbs = N'ksubscribers'
5. Start IIS and Kaseya services.
6. Access Kaseya VSA web interface and double check the database size reported under System > Statistics. 
7. Run Reapply Schema.