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
GO

SET 
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

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 code

SELECT 
@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_desc

IF 
@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:
RETURN 
@rc

Exec shrink