Friday 14 October 2016

SQL Tricks

How to Find Duplicate Record in the Table :-  Here I am taking InventItemBarCode Table which is having Item Variants Bar-code, Run below query into the SQL will give you record which is having more than one record.

SELECT ITEMBARCODE,ITEMID,[DESCRIPTION], COUNT(1) as Duplicate
FROM INVENTITEMBARCODE
GROUP BY ITEMBARCODE,ITEMID,[DESCRIPTION]

HAVING COUNT(1) > 1;


How to Shrink SQL database logs :- When you are facing Full disk problem in your Database Server Drive  and you don't have any junk files or folder in your Database Server, but system is showing Disk Full, due to this AX will not run and give you error while you run AX Application, Check database logs and run below SQL Query into you SQL,Change the Database name which logs want to shrink.

USE MicrosoftDynamicsAX;
ALTER DATABASE MicrosoftDynamicsAX
SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MicrosoftDynamicsAX_Log, 0);
ALTER DATABASE MicrosoftDynamicsAX
SET RECOVERY FULL;






How to Compare Two SQL Database :-  To compare two SQL Database free utility.