Get SQL name of Temp table Dynamics 365

Using code below in runnable class we can find temporary table name, which is created in SQL tempDB.

// Declare a TempDB table buffer

TmpCustName tCustName;

   

// Insert data to create the table in TempDB database in SQL

tCustName.AccountName = '10001';

tCustName.insert();

   

// Display the name

info(tCustName.getPhysicalTableName());

Reindexing Dynamis AX database

Below script will create job to re-index and update statistics of dynamics ax database.

In script below fill factor is used as 30%, review and adjust it as per your requirements.

This will be useful as part of Dynamics AX database maintenance.

Replace <Domain UserName> with domain user name.
Replace DatabaseName with database name.

 
USE [msdb]
GO

/****** Object:  Job [<DatabaseName> - Index and Statistics maintenance]    Script Date: 9/26/2017 12:28:53 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/26/2017 12:28:53 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaseName - Index and Statistics maintenance',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'<Domain UserName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update column statistics only]    Script Date: 9/26/2017 12:28:54 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update column statistics only',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=3,
  @on_success_step_id=0,
  @on_fail_action=3,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'SET NOCOUNT ON
DECLARE @init INT = 1, @cnt INT, @tsql nvarchar(max) = N''''
DECLARE @objectid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @statsname nvarchar(130);
DECLARE @tableStat table (id int identity(1,1), obj_id int, name varchar(1000))
INSERT INTO @tableStat
SELECT [object_id], name
FROM sys.stats
WHERE OBJECTPROPERTY([object_id], ''IsUserTable'') = 1
                AND (auto_created = 1 or user_created = 1)

SELECT @cnt = COUNT(1) FROM @tableStat

WHILE @init <= @cnt
BEGIN
select @objectid = obj_id, @statsname = name from @tableStat where id = @init

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SET @tsql = N''UPDATE STATISTICS '' + @schemaname + N''.'' + @objectname + N'' '' + @statsname + N'' WITH FULLSCAN'';
--PRINT @tsql
EXEC sp_executesql @tsql
SET @init += 1
END',
  @database_name=N'DatabaseName',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Reorganize, Rebuild indexes and Update statistics.]    Script Date: 9/26/2017 12:28:54 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reorganize, Rebuild indexes and Update statistics.',
  @step_id=2,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'--Index rebuild and reorg
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON -- 22/08/2015 added to avoid error due to xml and name identifier.
DECLARE @tsql NVARCHAR(MAX),@fillfactor INT=70,@init int = 1, @cnt int =0,@AvgFragmentationInPercent DECIMAL(6,2), @heap bit = 0
DECLARE @FragmentedIndexs TABLE (ID INT IDENTITY(1,1),IndexName VARCHAR(500),ObjectName VARCHAR(500),AvgFragmentationInPercent DECIMAL(6,2),FragmentCount INT,AvgFragmentSizeInPage DECIMAL(6,2),IndexDepth INT, index_id INT)

INSERT INTO @FragmentedIndexs
SELECT QUOTENAME(I.name) Name, QUOTENAME(DB_NAME())+''.''+QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id]))+''.''+QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,PS.avg_fragmentation_in_percent,
PS.fragment_count,PS.avg_fragment_size_in_pages,PS.index_depth, i.index_id FROM sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, ''LIMITED'') AS PS
INNER JOIN sys.indexes AS I ON PS.[object_id]= I.[object_id] AND PS.index_id = I.index_id WHERE PS.avg_fragmentation_in_percent > 10 --AND PS.index_type_desc = ''NONCLUSTERED INDEX''  
AND page_count > 0 AND page_count > 500 --AND i.index_id = 0 -- 22/08/2015 to exclude small tables having less than 500 pages for live database.
ORDER BY PS.avg_fragmentation_in_percent DESC

SELECT @cnt = COUNT(ID) FROM @FragmentedIndexs

WHILE @cnt >= @init
BEGIN
SELECT @AvgFragmentationInPercent=AvgFragmentationInPercent, @heap = CASE WHEN (IndexName IS NULL OR index_id = 0) THEN 1 ELSE 0 END FROM @FragmentedIndexs WHERE ID=@init

--Index rebuild
IF @AvgFragmentationInPercent<=30.00
BEGIN
 SELECT @tsql = STUFF((SELECT DISTINCT '';''+''ALTER INDEX ''+FI.IndexName+'' ON ''+FI.ObjectName+'' REORGANIZE ''
 FROM @FragmentedIndexs FI WHERE FI.AvgFragmentationInPercent <= 30 AND FI.ID = @init
 AND NOT EXISTS (SELECT * FROM @FragmentedIndexs X WHERE X.index_id = 0 AND FI.ObjectName = X.ObjectName)
 FOR XML PATH('''')), 1,1,'''')
END

ELSE IF @AvgFragmentationInPercent>30.00
BEGIN
 SELECT @tsql = STUFF((SELECT DISTINCT '';''+''ALTER INDEX ''+FI.IndexName+'' ON ''+FI.ObjectName+'' REBUILD WITH (FILLFACTOR = ''+CONVERT(VARCHAR(3),@fillfactor)+'') ''
 FROM @FragmentedIndexs FI WHERE FI.AvgFragmentationInPercent > 30 AND FI.ID = @init
 AND NOT EXISTS (SELECT * FROM @FragmentedIndexs X WHERE X.index_id = 0 AND FI.ObjectName = X.ObjectName)
 FOR XML PATH('''')), 1,1,'''')
END

--print @tsql
EXEC sp_executesql @tsql

--Update stats
IF @AvgFragmentationInPercent <= 30.00
BEGIN
SELECT @tsql = STUFF(( SELECT DISTINCT '';''+''UPDATE STATISTICS '' + FI.ObjectName + '' '' + FI.IndexName FROM @FragmentedIndexs FI
WHERE FI.AvgFragmentationInPercent <= 30 AND FI.ID = @init FOR XML PATH('''')), 1,1,'''')
--print @tsql
EXEC sp_executesql @tsql
END

--Table rebuild
IF @heap = 1
BEGIN
SELECT @tsql = STUFF(( SELECT DISTINCT '';''+''ALTER TABLE '' + FI.ObjectName + '' REBUILD'' FROM @FragmentedIndexs FI
WHERE FI.ID = @init AND index_id = 0 FOR XML PATH('''')), 1,1,'''')
--print @tsql
EXEC sp_executesql @tsql
END

SELECT @tsql = '''', @heap = 0
SET @init += 1
END
',
  @database_name=N'DatabaseName',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20140603,
  @active_end_date=99991231,
  @active_start_time=30000,
  @active_end_time=235959,
  @schedule_uid=N'b728c1ec-de6f-44bb-abee-dacc0049de00'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Move SQL Server database files

Create tempdb partitions and move to new location location.

Know existing location:
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Move to new location:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = "C:\SQLDB\tempdb.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = "C:\SQLDB\templog.ldf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev1, FILENAME = "C:\SQLDB\tempdb1.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog1, FILENAME = "C:\SQLDB\templog1.ldf")

Note:
Also verify that SQL service account has full access to new location.

To move msdb, need to follow steps below after executing above queries.

1. Go to SQL Server configuration manager.

2. Update path on parameters.

SQL Currently running queries

Execute below query to find currently running queries in SQL.

This can be helpful to identify blocking.

SELECT
      cast(axsess.context_info AS VARCHAR(128)) AS [Connection info],
      req.session_id
   ,blocking_session_id
      ,req.status
      ,req.total_elapsed_time / 1000.0 AS total_elapsed_time
      ,req.wait_type
   ,ses.host_name     
   ,DB_NAME(req.database_id) AS DB_NAME     
      ,sqltext.text     
   ,ses.login_name 
   ,req.command
   ,req.start_time
   ,req.cpu_time 
   ,req.command   
 FROM  sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
 JOIN  sys.dm_exec_sessions ses
     ON ses.session_id = req.session_id
      join 
  ---cast(axsess.context_info AS VARCHAR(128)) AS [Connection info] , *
  sys.dm_exec_sessions axsess
 on axsess.session_id = req.session_id
 where DB_NAME(req.database_id) = db_name()
 --WHERE         
 --axsess.program_name LIKE '%Dynamics%'
 --and axsess.status = 'running' 
 --and req.wait_type IS NOT NULL
 --and req.wait_type = 'OLEDB'