NOTE: The database optimization steps detailed below only apply to Microsoft SQL Server. If running Oracle, this is not needed.
Take the following steps on your current Track-It! database to ensure that the database indexes are optimized:
NOTE: It is recommended that this procedure be run at a slower time of the day, or even after hours since the re-building of the indexes could take several minutes to complete.
SQL Server 2005, 2008, and 2012:
- First, stop the Track-It! Configuration service (builds 8.1 and prior) or the Track-It! Service Management service (builds 8.5, 9, 10.x and 11).
- Download the attached TIA03326.zip file that can be used to re-build the indexes of all Track-It! tables.
- Extract the file in the zip to the Track-It! SQL server.
- Run SQL Server Query Analyzer (SQL 2000) or SQL 2005/2008/2012 Management Studio.
NOTE: If you do not have SQL Server Enterprise Manager or SQL Management Studio and their query tools, you can download SQL Server Management Studio Express:
To manage SQL Server 2000 and SQL 2005
Microsoft SQL Server 2005 Management Studio Express for 32-bit or 64-bit systems.
To manage SQL Server 2005 and SQL 2008
Microsoft SQL Server 2008 Management Studio Express for 32-bit or 64-bit systems.
These tools and will allow you to manage any MSDE, SQL Server Express, SQL Server 2000, SQL Server 2005, or SQL 2008 database.
- Login as an SA equivalent account and open a new query window.
- Copy the SQL script from the file that was extracted earlier into the new query window.
- Make sure the current Track-It! database is selected at the top, and then press F5 or click the play button (SQL 2000) or "Execute" button in SQL 2005/2008) at the top to execute the script.
- Next, backup the SQL transaction log and shrink the file:
- In SQL Server Query Analyzer run the following query (make sure to replace DBName with the name of the current Track-It! database). The command places the transaction log backup under the path specified so that can be changed if needed:
BACKUP LOG DBName
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBName.trn'
NOTE: A transaction log backup can only be done if the recovery model of the database is Full or Bulk Logged.
If you would also like to shrink the log file, the following command can be run to shrink the file to a specified size in MB:
DBCC SHRINKFILE(DBName_log, 100)
Once complete, make sure to restart the Track-It! services on the Track-It! application server.
- On the SQL Server, or a machine that has the database administration tools installed, start SQL Server Management Studio.
- Connect to the Track-It! database server and login with an SA equivalent account.
- Right-click the database and select Tasks -> Back Up.
- Take note of the recovery model of the database. If it is set to FULL or Bulk-Logged, select "Transaction Log" for the Backup Type. If the recovery model of the database is set to SIMPLE, this will not be available.
- Click Ok to begin the transaction log backup.
NOTE: A full backup must have been done prior to backing up the transaction log, or the backup will fail.
- Once the backup is complete, click Ok.
- Next, right-click the database again and select Tasks -> Shrink -> Files.
- Select "Log" from the File Type drop down list, and then click Ok.