SQL Database Maintenance

If you are at a company that is running Shavlik products on a full SQL environment and have a DBA on staff with SQL maintenance and backup policies already running against our DBs, great!  If you are running SQL Express or full SQL but don’t have a maintenance and backup plan in place, please keep reading.

A DB that has no maintenance procedures being run against it is likely the single biggest cause of an upgrade issue that is encountered and the root cause of many GUI performance issues that can be mitigated and, in many cases, resolved by proactive maintenance on the DB.  Below are our recommendations for good regular maintenance on your DB so you keep it running slim and clean for good performance and to reduce issues.

Keep in mind this is a starting point.  If you have regulatory needs that require more data kept live you should adjust to keep more data live.  If that is the case you may want to analyze how frequently you are scanning.  1000 agents scanning 8 times a day will grow your DB at a much more rapid rate than once per day or once per week.  And in most cases, you don’t really need all of that data.

Recommendation for regular DB maintenance:

Data Retention: Determine the amount of data that needs be kept on hand for operational purposes.  Typically 60-90 days is acceptable for operational purposes.  Configure PurgeOldProtectData utility to cleanup anything older than that number of days and schedule task to run monthly to clean up the DB.

http://supportteamblog.shavlik.com/2009/12/31/new-use-netchk-protect-7-2-to-purge-old-data-using-a-powershell-script/

Reporting: Determine what report data is required for auditregulatory requirements.  Run monthly reports fulfilling these needs and keep on file as far back as policy requires.  Typically 13 months is acceptable.

DB Backups: It is recommended to run weekly incremental and monthly full backups.  The backup should be run just before your scheduled purge.  Keep backups as far back as the reporting data.

DB Maintenance Schedule:

Backups: full monthly, just after patch maintenance for that month.  Incremental weekly, end of each week (after weekend patch windows preferably).

Purge Data: After Full Monthly backup is run

Reindex: After Purge Data is run

Integrity: After Reindex is run

Full SQL Maintenance Guidance:

If you are using full SQL it is easiest to setup maintenance plans using the maintenance wizard.  Microsoft has some documentation around common SQL maintenance at the following link including how to use the SQL Wizard to setup and maintenance plan:

http://www.networkworld.com/subnets/microsoft/110107-ch8-sql-server.html?page=2

If you are using SQL Express the maintenance wizard is not available.  In that case you can use the SQLCMD command line interface to run stored maintenance procedures or you may look into some tools created by DBAs to wrap these commands into an easier interface.  One tool that works very well is ExpressMaint.  Using either of these options you can write a script to handle the maintenance and schedule using the Microsoft Scheduler on the frequency you desire.

http://www.sqldbatips.com/showarticle.asp?ID=29

Example script for SQL Express to do a full backup, reindex, and integrity check using the ExpressMaint utility:

Expressmaint -S (local)SQLExpress -D ShavlikScans -T DB -R C:Expressmaint -RU WEEKS -RV 1 -B C:Expressmaint -BU WEEKS -BV 1 -V -C

ExpressMaint -S (local)SQLExpress -D ShavlikScans -T REINDEX -R C:Expressmaint -RU Weeks -RV 1

ExpressMaint -S (local)SQLExpress -D ShavlikScans -T CheckDB -R C:Expressmaint -RU Weeks -RV 1

– Chris Goettl

Edit: For NetChk Protect 7.8 Customers you should use the DB Maintenance Feature in the product.

Chris Goettl

Chris Goettl has over 15 years of experience in IT Management. He spent several years working in IT before joining Shavlik in 2004. Chris started in the Shavlik support team, supported OEM partners integrating Shavlik SDK's, worked in Sales as a Systems Engineer, and is now the Product Manager for the Shavlik Protect product line.