SQL related

Restart UAT services. https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/deployment/maintenanceoperationsguide-newinfrastructure#restart-services https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-debugdiag DevTest environment to connect to the UAT database https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-debugdiag ================================================================================================= NET START "MR2012ProcessService" NET START "DynamicsAxBatch" NET START "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" NET START "W3SVC" PAUSE NET STOP "MR2012ProcessService" NET STOP "DynamicsAxBatch" NET STOP "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" NET STOP "W3SVC" PAUSE ==================================================================================================== Shrink log file https://www.linkedin.com/pulse/transaction-log-database-full-easy-steps-resolve-ashish-kale/ ALTER DATABASE AXDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (PreProdDBBACKUP02_log, 1000) GO ALTER DATABASE AXDB SET RECOVERY FULL //to get log file name and keep the file name above USE AxDB GO EXEC sp_helpfile GO ===================================== K:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "K:\AosService\PackagesLocalDirectory" metadatadir "K:\AosService\PackagesLocalDirectory" -sqluser "axdbadmin" -sqlserver "." -sqldatabase "AxDB" -setupmode "sync" -syncmode "fullall" -isazuresql "false" -sqlpwd "**axdbadmin p/w from LCS env page***" pause update userinfo set ENABLE=1 where ID = 'Nicolas.Ramirez' Power Shell command CD "C:\Users\Admin281589b1f6\Desktop\SPE\sqlpackage-win7-x64-en-16.1.6374.0" .\SqlPackage.exe /a:import /sf:K:\Newfolder\me-uatbackup.bacpac /tsn:localhost /tdn:AxDB_UAT0222 /p:CommandTimeout=1200 /TargetTrustServerCertificate:True How to Backup And Restore Database In SQL Server https://www.systoolsgroup.com/updates/backup-and-restore-database-in-sql-server/ ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE AxDB MODIFY NAME = ORIG_4thJan2022; ALTER DATABASE ORIG_4thJan2022 SET MULTI_USER ============================================================================== DB properties >> Options >> Recovery model- Simple Shrink file >> change file type to Log DB backup >> Backup Options >> set backup compression - "Compress backup" To check which tables are occupying more data , RC on DB >> Reports >> Standard Reports>> Disk usage by top tables. Change DB in single user to multiuser Use master GO select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'AXDB' Get the spid, use side to kill. , execute below both statements at a time. GO Kill 59 GO ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE GO ========================================================================================================================= ALTER DATABASE AXDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (PreProdDBBACKUP02_log, 1000) GO ALTER DATABASE AXDB SET RECOVERY FULL //to get log file name and keep the file name above USE AxDB GO EXEC sp_helpfile GO ======================================================================================================================== --Find the logical name of database’s log file-- USE AXDB GO SELECT Name AS LogicalName, filename AS PhysicalFile FROM sys.sysfiles GO --SHRINKFILE command-- USE AXDB GO -- Shrink the truncated log file to 8 MB-- DBCC SHRINKFILE (AXDB_UAT2402, 8); GO USE AXDB GO -- Shrink the truncated log file to 8 MB-- DBCC SHRINKFILE (AXDB_UAT2402_log, 8); GO USE AxDB; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AxDB SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AXDB_UAT2402_log, 1); GO -- Reset the database recovery model. ALTER DATABASE AxDB SET RECOVERY FULL; GO ====================================== dbcc sqlperf(logspace)   select * from sys.database_files ALTER DATABASE AXDB SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (2, 1000); GO -- Reset the database recovery model. ALTER DATABASE AXDB SET RECOVERY FULL;  ===================================================== Compare Power BI reports C:\Temp>fc "Financial Analysis - Fiscal - Copy.pbix" "Financial Analysis - Fiscal (1).pbix" Comparing files Financial Analysis - Fiscal - Copy.pbix and FINANCIAL ANALYSIS - FISCAL (1).PBIX FC: no differences encountered