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.

Generating cross reference data during the build process

This post applies to AX2012 R2. Cross reference data can be generated as part of the Full AOT compile process or via batch process. However the Full AOT compile with cross reference takes much longer than a normal R2 compile (which is already much slower than R3 axbuild), so I prefer to do this step after the nightly integration build has completed.

Method 1 - Full AOT compile

Set options as below, or use the following SQL to accomplish the same. Next perform a Full AOT compile, and the cross reference data will be updated.

Here's the SQL for setting the option above
Only bit 5 of column DebugInfo needs to be set for cross reference to work. The other bits are assigned to other settings:

use MicrosoftDynamicsAX

-- Substitute the ID of your build service account here
DECLARE @UserId VARCHAR(255) = 'admin'

-- VIEW STATUS OF CROSS REFERENCE
SELECT
 Debuginfo as CurrentDebugValue,
 (Debuginfo & 65519) AS DebugValue_CrossReferenceOff,
 (Debuginfo | 16) AS DebugValue_CrossReferenceOn,
 CASE WHEN (Debuginfo & 16) > 0 THEN 1 ELSE 0 END AS CrossReferenceStatus,
 * 
FROM USERINFO 
WHERE id = @UserId

-- TURN OFF CROSS REFERENCE
UPDATE USERINFO 
 SET Debuginfo = (Debuginfo & 65519)
 WHERE id = @UserId

-- TURN ON CROSS REFERENCE
UPDATE USERINFO 
 SET Debuginfo = (Debuginfo | 16)
 WHERE id = @UserId

Method 2 - Batch cross reference update

A small but important detail to consider: there must be a batch AOS running. It will suffice for the AOS performing Full AOT compile to be added to the default batch group. Otherwise, the cross reference window will spin eternally without making any progress.

Here's code which will do that via job:

// Run cross reference from AOT job
Args xrefArgs = new Args("SysCompileAll");
xRefUpdate::startxRef(xrefArgs.pack());

Exporting and importing with BCP

Note that cross reference data does NOT travel with the Modelstore database. Instead this data lives in the transactional side. You will need to export the cross reference data if you want to be able to import it to development environments or elsewhere. Here's one way to do that with the BCP utility provided with SQL:

bcp MicrosoftDynamicsAx.dbo.XREFTABLERELATION out "$TargetFolder\$($FilePrefix)XREFTABLERELATION.DAT" 

bcp MicrosoftDynamicsAx.dbo.XREFREFERENCES out "$TargetFolder\$($FilePrefix)XREFREFERENCES.DAT"     

bcp MicrosoftDynamicsAx.dbo.XREFNAMES out "$TargetFolder\$($FilePrefix)XREFNAMES.DAT"         

bcp MicrosoftDynamicsAx.dbo.XREFPATHS out "$TargetFolder\$($FilePrefix)XREFPATHS.DAT"