It is a recommended practice to rebuild (reindex) FogBugz On-Premise database table indexes on a regular basis. If database table indexes are not rebuilt for an extended period, you might experience performance issues or even unexpected
500 Internal Error due to FogBugz not being able to retrieve the data from the database in a timely manner.
Recommended reading: FogBugz On-Premise Database Maintenance - Cheat Sheet to know more about Microsoft SQL Database maintenance, index fragmentation, and index rebuild.
Running an SQL database for a long time will make the table indexes fragmented on the disk. The database engine will start to run slower as the database and indexes grow until FogBugz queries take longer than 30 seconds to resolve. This contributes to unwanted timeouts and JSON API errors on the interface.
There are two ways to quickly check if your indexes are fragmented and you might need to rebuild database table indexes:
- do a simple select on the tables typically containing the biggest amount of data
- you experience from time-to-time
500 Internal Error
Do a simple select on a table containing a big amount of data
Perform a select query on the FogBugz database (via SSMS) on one of the following tables: Bug, BugEvent, Attachment.
USE Trial1 SELECT TOP 500 * FROM Attachment
Note: If the system takes more than 30 seconds to get results for the first 500 rows, then it indicates that the SQL database performance has been degraded.
500 Internal Error
Due to the poor data retrieval performance of the database, you might experience some slowness when accessing cases and encounter multiple JSON API errors or 500 Internal Error timeouts. Something like this:
|JSON API ERROR: Could not load /api/0/notifications/20
500 Internal Error
- Administrator access to the FogBugz On-Premise Database
- Microsoft SQL Server Management Studio (SSMS)
NOTE: The index rebuild process is a "heavy" process. It might take a long time to execute, and it will put a high load on your database server. Before starting it, please check the following points:
- Please read what other known issues are causing slow performance. Maybe your issue does not need the heavy process of a full database table reindexing.
- Execute this task outside business hours to minimize the impact on the end-users. FogBugz will not be available during the table index rebuild process.
- Because it puts a high load on your database server, be prepared that during the index rebuild, other applications using the same database server might be impacted performance-wise.
- If your database recovery model is set to Full, index rebuild will result in a huge transaction log size increase. Please make sure you have enough storage space available.
Rebuilding the Indexes
Before jumping into the script, please follow these steps for a safer execution:
Issue a FogBugz database backup before running the index rebuild task. It is highly recommended to make a backup (or take a snapshot, if you are working on a virtual environment) before making any changes to the production data.
Run a database report to identify the size of the data currently stored in your SQL server. When connected to the server with SSMS, right-click on the FogBugz database (ex: trial1) and select Reports > Standard Reports > Disk Usage by Table:
Reference: Identifying the Size of FogBugz Database
Using SSMS, run the rebuild_indexes.sql script attached to this article and wait for it to finish. Before running it, change
Trial1to the name of your FogBugz database.
Re-run the Disk Usage by Table report
Re-run the Disk Usage by Table report to compare results. If the index rebuild was successful, you should see a significant index size decrease when comparing the indexes before and after the rebuilding process, mostly for the bigger sized tables. For some smaller tables, the index size will decrease only a small amount, or not at all. This is normal.
You should experience improved performance also while using the FogBugz application.
If you still experience performance issues, please read what other known issues are causing slow performance.
It is highly recommended to configure a routine maintenance task on your SQL server to run a scheduled SQL server index reorganization and rebuild process on the FogBugz Database.
While it is not mandatory (and out of FogBugz's scope of support), it is an excellent server administration practice that can give significant performance improvements on FogBugz with minimum downtime. This practice keeps your database file working at top performance and prevents any of the unwanted symptoms of a low-performance database system.
- FogBugz On-Premise Database Maintenance - Cheat Sheet
- Backup Database (FogBugz On-Premise)
- FogBugz On Premises: Known Issues Causing Slow Performance