Shrink database SQL Query

Shrink database data.

USE DatabaseName;
GO
-- Shrink the truncated file to 1024MB.
DBCC SHRINKFILE (DataBaseFileName, 1024);
GO

Shrink database log.

USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseFileLogName, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Advertisements

Reindex All Table On Database SQL Query

Script to re-index All Table On Database.

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name IN ('DATABASENAME')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

		Print  ('ALTER INDEX ALL ON ' + @Table)
		SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
		EXEC (@cmd) 

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

get Report to Position Worker Name with Employment Recid SQL Query

Example code :

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
	(select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
	INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
	AND HCMPOSITION.RECID = 
		(select TOP 1 ParentPosition from  hcmPositionHierarchy 
			INNER JOIN  hcmPositionHierarchyType on hcmPositionHierarchy.PositionHierarchyType = hcmPositionHierarchyType.RECID AND hcmPositionHierarchyType.HierarchyType = 0
			where hcmPositionHierarchy.Position = 
				(select top 1 POSITION from HCMPOSITIONWORKERASSIGNMENT  
				INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
				where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO 
				AND HCMPOSITIONWORKERASSIGNMENT.WORKER = 
					(select TOP 1 WORKER from hcmEmployment where hcmEmployment.RECID = 5637152077)))
	where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

get worker name from position with SQL Queries

example code :

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
	(select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
	INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
	AND HCMPOSITION.POSITIONID = 'P0003' 
	where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

error: Cannot create record. The Record already exists.

Problem :
Our client recently facing this error during creating any journal lines (LedgerJournalTrans) .

we did restart aos, full CIL, and any standard technical troubleshoot hoping it was a cache error or something like that. And we tried drop the table and inject it with the copied data. After doing some checking on table SystemSequences with table id 212 (ledgerJournalTrans), its nextVal RecId value record is already exist on that table.

reference for RecId on AX :http://www.axaptapedia.com/RecId

solution :
1. Stop the AOS
2. get the max recid from the table on SQL with:

select max(recId) from ledgerjournaltrans;

3. update the systemSequences table on SQL with:

update SYSTEMSEQUENCES set NEXTVAL = 'MAXRECID from table' where tabid= 'tableID'

4. restart AOS and run AX

Conclusion :
they did not doing some sort of AX Version upgrade that will impact whole data
and the main problem why the AX stored procedure on SQL returning next RecId value for the next record that already exist on the table remains a mystery.

Reference :
1. https://community.dynamics.com/ax/f/33/t/109379
2. https://waytoax.wordpress.com/2014/06/01/cannot-create-a-record-in-table-the-record-already-exists-2/

Search and get table name with parameter table name and have data inside. SQL

this query is useful to check table name with data inside after running the class SysDatabaseTransDelete.

i put another parameter table name, for only search only customize table on AX with Developer identifier.

preview :
Capture

code below

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'TID%'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF 
	(@table_name LIKE @search_string)
	BEGIN
		SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ')'+ 'PRINT ''' + @table_name +''''
		EXECUTE(@sql_string)
	END
    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

refresh DB Sync error log

i found error log that make no sense after DB sync on AX 2012 R2 CU7. the table that the error pointed to already deleted, but the error still occurred.

here is the solution :
“If you are getting synchronization errors that do no make sense or that you think you should have already resolved there may be old records left in the SQLsyncinfo table or have old or incorrect object ID references. If you suspect this make a database backup, shutdown the AOS, in SQL run truncate table SQLSYNCINFO, start the AOS and run a new Synchronize from the AOT.”

source : http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx