Agresso Database Servers – Maintenance Checklist

This is some information that was not made available to us, the DBA team, on a site I worked at recently.  I suspect that I was not in a unique situation, so I’m providing the DB Server parts of those lists here.

  • Daily
    • Check Agresso database backup to disk has been successful
    • Check backup to tape
    • Check SQL Server Error Log (or Oracle Alert Log)
    • On Oracle Systems, clear out archive logs
  • Weekly
    • Check free space on all drives – see notes
  • Monthly
    • Do disaster recovery from tape to disk, then restore the disk backup to a separate database
    • Shrink Agresso data file and transaction log – see notes
    • Clear temp tables in Agresso database and temporary databases – see notes
    • Rebuild Agresso database indexes – see notes
    • Clear out report tables – see notes
  • Monitoring
    • Check for long-running queries
    • Check for excessive deadlocking
    • Check CPU usage
    • Check memory usage
    • Use performance monitor counters to monitor disk queue length and cache hit ratio
    • Defrag hard drives

This is a fairly standard list of maintenance tasks.  However, I would take issue with a few items on this list:

Check free space on drives – I believe this should form part of the routine monitoring, rather than a weekly check.  There’s even an extended stored procedure (xp_fixeddrives) that gives the drive letters & free space in MB.

Shrink data file and transaction log – Generally speaking I would avoid doing this, unless there were significant disk pressures on the server, and the database file sizes were significantly larger than required for the foreseeable future.  Of course, you should be monitoring DB file size and usage regularly…  and you need to be careful when doing the shrinks, if you do them – they are disk intensive tasks, and will impact the performance of your database, and you’ll need to reorganize the data and indexes afterwards, as they will be fragmented by the shrink.  Check the warnings from Paul Randal – the guy that wrote the code.  (His Myth-a-day series is well worth a read…)

Clear temp tables in Agresso database and temporary databases & clearing report tables – Agresso can be configured to have its own temp database.  This may (or may not) be a good idea; however, if you are using the non-standard TempDB, you’ll need to keep on top of the structures in the AgrTempDB, as these won’t be deleted when sessions end, or the database recreated when the server is restarted, as is the behaviour of the normal TempDB.  Note also that there are temporary tables in the main Agresso database – check for tables created recently; check also for tables created by unexpected users.

Rebuild indexes – you may need to do this weekly rather than monthly.  Monitor performance of the database, and monitor the state of the indexes with the sys.dm_db_index_physical_stats DMV.  There are tools out there that make use of this information to provide more intelligent reindexing than the standard Maintenance Plan task, for example:

Of course, we don’t have the useful stuff, such as scripts to automatically clear down the temporary / report tables, but it’s a start…

This entry was posted in SQLServerPedia Syndication and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.