Thursday 30 July 2015

Dynamics Ax Data Size Increase and Slow,Steps to Reduce Data Size Shrink Logs

  1. Log into Microsoft SQL Server Management Studio
  2. Right click on your database.
  3. Select Properties.
  4. Click on Options.
  5. Change the recovery model to Simple.
  6. Click OK.
  7. Right click on the database.
  8. Select Tasks > Shrink > Database.
  9. Click OK.
  10. Right click on the database.
  11. Select Properties.
  12. Click on Options.
  13. Change the recovery model to Full.
  14. Click OK.

SSMS Steps To Shrink a SQL Log File

  • 1. Go to Start menu->All Programs-> SQL Server Management Studio->Object Explorer
ssms objects

  • Click on Databases so that it Expands and Select the Database which you want to Shrink
  • Then make a Right Click Go to Task-> Shrink-> Files.
shrink sql log file
  • A "Shrink Window" appears Select File type i.e. Log and moves to downward and select "Reorganize Page before releasing unused page" and choose the Size for Log File.
sql log shrink window

If You Dont Want to follow above steps then Copy below query and paste in to Sql Query Window


SET ANSI_NULLS ON
GOSET 
QUOTED_IDENTIFIER ON
GO
CREATE PROC 
[dbo].[shrink]
   
@db_name SYSNAME = NULL
  , 
@target_size_mb INT = 2
  
@backup_location NVARCHAR(200NULL
  , 
@backup_file_name NVARCHAR(200NULL
  , 
@maximum_attempts INT = 10
  
AS
SET NOCOUNT ON
ALTER DATABASE [XXXXX] SET RECOVERY SIMPLE
SELECT 
@db_name COALESCE(@db_nameDB_NAME())
     
DECLARE 
@logical_log_file_name SYSNAME,
        
@backup_log_sql NVARCHAR(MAX),
        
@shrink_sql NVARCHAR(MAX),
        
@checkpoint_sql NVARCHAR(MAX),
        
@db_id INT = DB_ID (@db_name),
        
@start_size_mb INT,
        
@final_size_mb INT,
        
@attempts INT = 0,
        
@recovery_model INT,
        
@recovery_model_desc SYSNAME,
        
@rc INT = -- return codeSELECT @logical_log_file_name name,
       
@start_size_mb size 128
   
FROM MASTER..sysaltfiles
   
WHERE dbid=@db_id AND  fileid=2
  
SELECT 
@recovery_model recovery_model   
     
@recovery_model_desc recovery_model_desc
   
FROM sys.databases
   
WHERE database_id=@db_id
   
PRINT 
'Starting size of [' @db_name '].[' 
            
@logical_log_file_name 
            
'] is ' 
            
CONVERT(VARCHAR(20), @start_size_mb) + ' MB '
            
' recovery model = ' @recovery_model_descIF @start_size_mb <= @target_size_mb BEGIN
   PRINT 
'['+@db_name+'] does not need shrinking'
    
END
  ELSE BEGIN    
         
    IF 
@recovery_model != 3
        
AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
        RAISERROR 
('Null backup file location or name. aborting.'161)
        
SET @rc 50000
        
GOTO get_out
    
END
   WHILE 
@attempts @maximum_attempts
      
AND @target_size_mb < (SELECT CONVERT(INTsize/128FROM MASTER..sysaltfiles 
                               
WHERE dbid @db_id AND 
                                      
name @logical_log_file_name-- not target
      
BEGIN
        SET 
@attempts @attempts 1
        
        
IF @recovery_modelBEGIN
           SET 
@checkpoint_sql 'use ['+@db_name+']; '
                               
'checkpoint'
           
PRINT @checkpoint_sql
           
EXEC (@checkpoint_sql)
            
END
        ELSE BEGIN
           SET 
@backup_log_sql =  'BACKUP LOG ['@db_name '] '
                              
' to disk = ''' @backup_location 
                              
CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\' 
                                    
THEN '' ELSE '\' END
                              
@backup_file_name 
                              
CONVERT(VARCHAR(10), @attempts
                             + 
'.trn'''
           
PRINT @backup_log_sql               

           
EXEC (@backup_log_sql-- See if a trunc of the log shrinks it.
        
END
               
       SET 
@shrink_sql 'use ['+@db_name+'];'
                       
'dbcc shrinkfile (['+@logical_log_file_name+'], '
                       
CONVERT(VARCHAR(20), @target_size_mb) + ')'
       
EXEC (@shrink_sql)
    
END
END 

SELECT 
@final_size_mb size/128 
   
FROM MASTER..sysaltfiles 
   
WHERE dbid @db_id AND name @logical_log_file_name
   
PRINT  
'Final size of [' @db_name '].[' 
            
@logical_log_file_name 
            
'] is ' +
       
CONVERT(VARCHAR(20),@final_size_mb)
       + 
' MB'
    
get_out:

ALTER DATABASE [XXXXX] SET RECOVERY FULL
RETURN 
@rc

Exec shrink

Wednesday 29 July 2015

Errors CIL Generation, Database Synchronize in AX 2012

A Parameter Could Not Be Serialized

For a quick resolve: Uncheck option "Excute business operations in CIL"  under Tools-> Options -> Development tab.












The reason is that you haven't been able to do a successful FULL CIL compile.
Run again and try to fix the error in CIL.


CIL Generation Source Array Was Not Long Enough
  1. Stop the AOS.
  2. Delete all of the source in the C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\XppIL directory.
  3. Start the AOS.
  4. Perform a full CIL generation.
  5. (Only do if errors are still there and you have no error when compile X++, just comment the code notice in Compiler output with /*..*/, recompile CIL and delete comment tag)

CIL generation error : The given key was not present in the dictionary

  1. Check the CIL log file, generally located at "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\XppIL\Dynamics.Ax.Application.dll.log"
  2. You will find the AOT object for which the CIL generator found the error.
  3. Compile that object, fix the error, or delete (if needed) and then regenerate the IL.
  4. (Only do if errors are still there : Stop AOS > Delete the .auc file from users\\Appdata  folder > Restart AOS)

DB synchronize error: "Field mismatch in union query. Field ... is not compatible with field 

Check your EDT concern to that field ( in query, table, view). They have different size.
So you just change the size to be the same.
Or check someone changed size of standard EDT. It's the reason cause this error

Duplicate type with name 'Dynamics.Ax.application.' in assembly 'Dynamics.Ax.application 

1. Stop the ax service.

2. Truncate the SysXPPAssembly in db model
3. Delete all the files in below directory in server where AOS hosted:

C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\XppIL

4. Start the service

5. Compile full CIL



The CIL generator found errors and could not save the new assembly



Step 1: Stop the AOS

Atep 2: Delete all of the source in the C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\XppIL directory

When you do this, just delete all the files within the XppIL folder that are outside of other folders. 



Step 3: Start the AOS


Step 3: Perform a full compile (it will not work without doing this)


Step 4: Perform a full CIL generation


The drawback of this fix is that it takes a long time to complete. However, this fixes the issue, which is the desired outcome, and the services deployment and incremental CIL compilations moving forward would be error free.

As you can see, the service was deployed correctly and if I opened my inbound port I'll see it there.


UPDATE:

After a FULL CIL compilation I got the following errors:
 
AOT > Class> ReleaseUpdateDB41_Basic
 
 
I just went to the object in question and compiled them separately.

 
 
The outcome would correctly compiled all the artifacts, including my new service gorup.