First and foremost, make sure that your Scheduled Tasks include a task called "Purge Journal Messages." It should run once per day and purge messages roughly older than two weeks. Because we track ALL logger communications, if you don't have this set up, your database will fill up with this unnecessary data very quickly.

Also, if you are collecting minute data, you should have a plan to routinely archive and purge this data from the database, or use the Average Data Purge task in the Task Scheduler to do this for you.


If you've taken those two simple steps, we can then advance to database issues:

Transaction Log
SQL databases can get very large over time, in particular the transaction log (.LDF) file, when the database is set to FULL_RECOVERY mode (the default).

The FULL_RECOVERY mode is useful in cases where you want to be able to 'roll back; the database to any point in time (but, of course, the files have to be intact- it's no help when trying to do a disaster / file loss recovery). However, if we are doing regular backups, then using FULL_RECOVERY isn't all that necessary, and the transaction log becomes an annoyance. In fact, it can be 3x to 10x the size of your main database, so we don't recommend it.

If you wish, you can go into the database properties in SQL Server Management Studio and change your database from FULL_RECOVERY to SIMPLE_RECOVERY. Right-click on AVData, select 'Properties', select 'Options' and change Recovery Model (at the top) from FULL to SIMPLE.

If you have taken this step, you will also need to manually clear the transaction log file and make a new one. The best procedure to do this is:

1. Stop the AirVision Service
2. Open SQL Management Studio
3. Right-click the database (AVData) and select Tasks->Detach
4. Open the data folder (C:\Program Files\SQL Server\.... (look around)...\Data)
5. Find the AVData_log.LDF file and rename it to AVData_old
6. In SQL Management Studio, right-click on the "Databases" folder and select "Attach"
7. Click the "Add" button and navigate to the AVData.mdf file.
8. Remove the reference to the log/LDF file and click "OK"
   
9. Restart the AirVision Service.
   

You may also use the following SQL script to occasionally clear the log file, once you are in SIMPLE mode. This script may be used in this form in SQL Server Management Studio:

alter database AVData set recovery simple
go
dbcc shrinkdatabase (AVData,10)
go
alter database AVData set recovery full
go

Index Rebuilds
SQL maintains a series of referential indexes to quickly find data. While SQL does some work to try to 'recycle' this space, it's not very good at it. A simple script can be run to rebuild the indexes and shrink the database, sometimes resulting in 50% to 80% size decreases on a database that has never had this done. We recommend this to be done every 6-12 months, and is essential on a SQLExpress system (such as an AV-Trend site computer or 8872 Logger). For a site PC / logger, this can be set up as a scheduled SQL task to run every 7-30 days. Be advised that while this is running, access to editors and reports will be significantly slower, so it is recommended this process run in the middle of the night. The script (manual or automated) would be:

ALTER INDEX ALL ON ReadingAverageData REBUILD
DBCC SHRINKDATABASE (AVData)

This script can run during normal polling, but should not be run at the same time as a database backup.

(We have tested this task running every 6 minutes against a system doing minute polls without any significant interference. The system will 'buffer up' polling results until the database becomes available, but may not be able to insert the data until after the index rebuild and shrink are completed, which may take several minutes or up to an hour for very large databases).