Database Sync - The transaction log for database 'MicrosoftDynamicsAX' is full due to 'ACTIVE_TRANSACTION'.

During database sync, the following statement resulted in an error.

CREATE INDEX I_587PROJID_IDX ON "DBO".PROJEMPLTRANS (PROJID,PARTITION,DATAAREAID) INCLUDE (TRANSDATE)

Synchronize database - Cannot execute a data definition language command on ().
The SQL database has issued an error. SQL error description: The transaction log for database 'MicrosoftDynamicsAX' is full due to 'ACTIVE_TRANSACTION'.

Post Mortem - What happened?

Basically, the SQL server was unable to service the request for space during the creation of the new or modified index. This failed the DB sync process. This could be due to several factors. Here are a couple of methods for managing disk space when working in an over-subscribed environment, where disk space is limited.

Modelstore space utilization

During deployments, the Modelstore database will see heavy writes. This requires 40 GB space for log file growth. This space can be reclaimed after the Modelstore import is finished. Set the database to simple recovery mode and shrink the database log file. In test environments, I do this before taking a backup so that developers will have enough disk space to restore it. This saves each developer 40 GB of space.

Transactional space utilization

The transactional database will see some heavy traffic during database sync and table operations. This will vary depending on the scope of database changes, and how long since last deployed. For deploying to test environments, I set the transactional database to simple recovery model before performing database sync. Afterward I set it back to full recovery and then take a full backup.

Take away

Don't shrink things in production. However it may be useful to manage space differently during the deployment process. This will help avoid issues such as running out of disk space during Modelstore import or database sync, and it will save disk space downstream for environments used in development. Multiply this by the number of branches being tested in parallel and the number of test environments, and it may add up to significant savings.

Also, monitor the space used when staging a deployment during the database sync step. Always stage deployments against recent production data -- this will give a much better idea of the space and time required. Every deployment is different depending on the changes made and how large the affected tables are.