Follow

How To: Database Maintenance

Note: Maintenance Plan does not need to be implemented in a SQL Schedule post-9.3 as this is already implemented to the existing maintenance.

 

This maintenance will revitalize and get your database running smoother. However, please note this needs to be done during a maintenance window as each step can take a very long time. The time it takes to process depends on the current database size and the performance of the SQL Server. I would recommend setting a large block of time committed to doing this.

 

1) Stop all Kaseya-related services and IIS.

2) Backup your SQL Database through SQL Server Manager.

3) Run each script located in database-cleanup.zip, simply open each script and execute them in SQL Server Manager. (Note: Some of these may take a long time to run)

4) Once all those scripts are ran, unzip database-maintenance.zip and run the shrinkTransactionLog.sql script. 

5) Next, run the shrink database sql script. (Note: This may take a long time to run.)

6) Next, run the numbered scripts in the numbered order. (Note: It is common that these scripts return "red" text but does not indicate an error. Furthermore, if the stored procedures already exist, the first two scripts will return an error, and can be safely ignored.)

7) run the shrinkTransactionLog.sql script. again.

8) Finally, restart all Kaseya-related services and IIS.

 

Additionally, you can take the scripts in database-cleanup.zip and create a reoccurring database maintenance plan in SQL Server Manager to keep your database from growing too large in the future:

unnamed.png

 

For information on how to create a database maintenance plan, please see here.

Was this article helpful?
9 out of 9 found this helpful
Have more questions? Submit a request

7 Comments

  • 0
    Avatar
    Gavin Steiner

    Thanks Gonzalo!

    Do you ever take a break? ;-)

  • 0
    Avatar
    Rusty Franks

    I've been looking for something like this for awhile. Thanks Gonzalo

  • 0
    Avatar
    Dragos Viulet

    What is the difference between the "Run database backup / maintenance every" option from the System> Server Management>Configure and the maintenance scripts available here? Is one preferable to another? Are they fundamentally different?

    We have set the Run database backup / maintenance every to run daily. How often do you think we should run the maintenance scripts available here?

     

    Thanks,

    Dragos Viulet

  • 0
    Avatar
    Alex

    Awesome, this got our DB from 80Gb to 40Gb, thank you!

  • 0
    Avatar
    Eddie Vasquez

    My only question is step 1 where we stop all Kaseya-related services and IIS. Would that be performed on the SQL Server side or the App server side?

  • 0
    Avatar
    Gonzalo Carrillo

    @Gavin - never, ever.

     

    @Rusty Franks - Cheers.

     

    @Dragos Viulet  - Not much difference, this is just the method to manually kick it off, it is not recommended to wait until kaseya runs it when doing a maintenance, it must be performed directly after.

     

    @Alex you are welcome.

     

    @Eddie Vasquez - App server

  • 1
    Avatar
    Brian Dagan

    Back in ticket #54599, I was told by John Nuttall that KSupport (going forward) should NEVER recommend a DB shrink except in very limited circumstances.  Here's the exact exchange:

    Me: "Please, please, please don't recommend a DB shrink as part of your support SOP... I had to terminate ours after the frustrating process of figuring out that it was going to take 18(!!!!!) hours to complete. There are many DBAs that absolutely recommend /against/ doing a DB shrink. Even with our frontend server shut down, an 18+ hour downtime is not acceptable--I'll just leave the empty space in the database, thanks. Is this something you can communicate with the Support folks?"

    John: "Agreed, as with (1), a shrink DB is a valid solution to some issues - "My DB is 200 gig and i am out of disk space!", but these are specific cases, and again, this needs to be explained to the customer. If they are down due to no disk space, then there is no other solution."

    Virtually NO SQL DBAs recommend that you run a DB shrink--and "This may take a long time to run" does not adequately communicate the true extent of the impact (18+ hours for us back when we had ~8,000 Agents).

    I would strongly urge caution for anyone with a larger KServer footprint to take these instructions with a massive grain of salt... and I'd urge KSupport to re-examine their processes!

Article is closed for comments.