NOTE: The database optimization steps detailed below only apply to Microsoft SQL Server. If running Oracle, this is not needed.
Instructions:
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.
- 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 "Database Re-Indexing.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/2019 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.
To manage SQL Server 2019 https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
- These tools and will allow you to manage any MSDE, SQL Server Express, SQL Server 2000, SQL Server 2005, or SQL 2008 or 2019 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/2019) at the top to execute the script.
- Next, backup the SQL transaction log and shrink the file:
SQL 2000 - 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'
GO
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)
SQL Server 2005, 2008, 2012 and 2019:
- 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.
Once complete, make sure to restart the Track-It! services on the Track-It! application server. |