As you probably know, manual sweep (by invoking gfix -sweep) is the important part of Firebird database maintenance (especially for big databases). Unfortunately, there are few people who understand the internals of sweep process. In this post we will not explain the magic of sweep, because it requires long and detailed explanation, instead of this we will provide you with the simple method to check that sweep was completed successfully and fulfilled its task.
After running gfix -sweep you need to run any Firebird client (like isql.exe) and commit at least one transaction - this is necessary to move other transactions' markers after sweep.
Then run gstat -h and check its output: all transaction markers should be aligned (i.e., with minimal gap):
Also, the big gap is an obvious alert to check database statistics (produced by gstat -r, visualized by IBAnalyst).
The most often reason why sweep does not clear all record versions is long-running writeable transaction (and this is the most often reason why automatic sweep does not work well), but there are other unpleasant options, like database corruption.
If you are sure that there were no connected users during sweep, or you saw that sweep finished unusually quickly for the big database (like several second for database 5+Gb in size), consider it as an alert, and run validation (gfix -v -full) as soon as possible.
Disadvantage of such approach (in terms of recognizing problem in system area of Firebird with failed sweep) is that sweep usually scheduled to run once per day, and it requires explicit attention of administrator, because only indirect signs appear. To monitor database health around the clock we are using our FBDataGuard tool - it check the same metadata that sweep touches during sweeping, and it sends alert immediately if something is wrong.
After running gfix -sweep you need to run any Firebird client (like isql.exe) and commit at least one transaction - this is necessary to move other transactions' markers after sweep.
Then run gstat -h and check its output: all transaction markers should be aligned (i.e., with minimal gap):
Oldest transaction 16702
Oldest active 16703
Oldest snapshot 16703
Next transaction 16704
If you see that gap is more than several transactions, it means that sweep did not remove all possible garbage (and in this case you can see gap of hundreds/thousands transactions). Unfortunately, sweep does not produce any errors or messages in firebird.log, so it's hard to determine the reason of failure.Oldest active 16703
Oldest snapshot 16703
Next transaction 16704
Also, the big gap is an obvious alert to check database statistics (produced by gstat -r, visualized by IBAnalyst).
The most often reason why sweep does not clear all record versions is long-running writeable transaction (and this is the most often reason why automatic sweep does not work well), but there are other unpleasant options, like database corruption.
If you are sure that there were no connected users during sweep, or you saw that sweep finished unusually quickly for the big database (like several second for database 5+Gb in size), consider it as an alert, and run validation (gfix -v -full) as soon as possible.
Disadvantage of such approach (in terms of recognizing problem in system area of Firebird with failed sweep) is that sweep usually scheduled to run once per day, and it requires explicit attention of administrator, because only indirect signs appear. To monitor database health around the clock we are using our FBDataGuard tool - it check the same metadata that sweep touches during sweeping, and it sends alert immediately if something is wrong.
