Cleaning up the CA Database
What rows can we safely delete from the CA database? First, you need to have a basic understanding of what exactly is stored in the CA database. When a new certificate request is submitted to the CA a new row is created in the database. As that request is processed by the CA the various fields in that row are updated and the status of each request at a particular point in time describes at what point in the process the request is. What are the possible states for each row?
- Pending – A pending request is basically on hold until an Administrator manually approves the request. When approved, the request is re-submitted to the CA to be processed. On a Standalone CA, all certificate requests are pended by default. On an Enterprise CA, certificate requests are pended if the option to require CA Manager approval is selected in the certificate template.
- Failed – A failed request is one that has been denied by the CA because the request isn’t suitable per the CA’s policy, or there was an error encountered while generating the certificate. One example of such an error is if the certificate template is configured to require key archival, but no Key Recovery Agents are configured on the CA. Such a request will fail.
- Issued – The request has been processed successfully and the certificate has been issued.
- Revoked – The certificate request has been processed and the certificate issued, but the administrator has revoked the certificate.
In addition, issued and revoked certificates can either be time valid or expired.
Delete any Unnecessary Rows from the Database
Issued certificates should not be deleted from the CA until they expire, while revoked certificates should not be deleted because they feed the contents of the certificate revocation list.
The certificate revocation list is a list maintained by the certification authority and provides the list of revoked certificates to consumers of digital certificates, so that they can perform revocation tests before accepting the presented certificate.
You can delete expired certificates that exist beyond their validity period without any side effects.
The two types of records that you can delete at any time are:
- Certificates issued and expired
- Revoked and expired certificates
In addition, refused and pending requests can be deleted. They are only requests for certificates, and no issued certificate is associated with them.
To delete records, this is done using the certutil command line along with the deleterow parameter. You need to specify the type of the records to be deleted according to the below table
For example, if you want to delete all failed and pending requests submitted by January 22, 2010, the command is:
Certutil -deleterow 1/22/2010 Request
[date in mm/dd/yyyy format]
Note: The only problem with this approach is that certutil.exe will only delete about 2,000 – 3,000 records at a time before failing due to exhaustion of the version store.
Backup the Database
There are few prerequisites to complete before beginning compaction:
- Make sure you have enough free space on the partition equal to or greater than the database file size. If you do not have enough space, you must move the database and log files to a partition with enough free space.
- Make sure you have a good backup of the CA database prior to starting just in case something goes wrong. The added benefit to backing up the CA database is that you’ll truncate the database log files.
- To back up just the CA database, run the following command:
C:\>Certutil -backupDB backupDirectory
The backup directory will be created for you if it does not already exist, but if it does exist, it must be empty. Once you have the backup, copy it somewhere safe. And now we’re finally ready to proceed.
Compact the Database
The final step in this process is compacting the CA database file to remove all the white space resulting from deleting the failed requests from the database. This process is identical to defragmenting and compacting Active Directory’s ntds.dit file, as the Certificate Services uses the same underlying database technology as Active Directory — the Extensible Storage Engine (ESE).
During the certificate database compaction, a new database file is created and all the active records are copied from the old database file to the new database file, thus removing any of the white space. When finished, the old database file is deleted and the new file is renamed in place with the name of the old file. While actually performing the compaction, Certificate Services must be disabled.
To compact the CA database, stop and then disable Certificate Services. The CA cannot be online during this process.
Next, run the following command:
C:\>Esentutl /d Path\CaDatabase.edb
Esentutl.exe will take care of the rest. In the background, esentutl.exe will create a temporary database file and copy all the active records from the current database file to the new one. When the process is complete, the original database file will be deleted and the temporary file renamed to match the original. The only difference is that the database file should be much smaller.
To finish, copy the now much smaller database and log files to the original drive and then re-enabled and restarted Certificate Services.
