Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Change to indexdefragmentation script

My take on Muthukkumaran Kaliyamoorhty’s script, deals with schemas for indexes and tables.

Ran from stored job to rebuild or reorganize and update stats depending on if they need it or not.

Build history table:

USE [msdb]
GO

/****** Object: Table [dbo].[dba_defrag_maintenance_history] Script Date: 05/13/2014 10:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[dba_defrag_maintenance_history](
[db_name] [sysname] NOT NULL,
[table_name] [sysname] NOT NULL,
[index_name] [sysname] NOT NULL,
[frag] [float] NULL,
[page] [int] NULL,
[action_taken] [varchar](35) NULL,
[date] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[dba_defrag_maintenance_history] ADD DEFAULT (getdate()) FOR [date]
GO

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[indexdefragmentation] Script Date: 05/13/2014 09:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary: Remove the Index Fragmentation to improve the query performance
Contact: Muthukkumaran Kaliyamoorhty SQL DBA
Description: This Sproc will take the fragmentation details and do four kinds of work.
1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
3. Check the fragmentation between 15% to 29% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the three conditions is false
ChangeLog:
Date Coder Description
2011-11-23 Muthukkumaran Kaliyamoorhty created

*************************All the SQL keywords should be written in upper case*************************

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schema_name SYSNAME
)

EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schema_name)
SELECT
T.Name AS obj,
I.[name] AS ind,
f.avg_fragmentation_in_percent,
f.page_count,
SCHEMA_NAME(T.Schema_ID) AS table_schema
FROM sys.tables T WITH (NOLOCK)
LEFT OUTER JOIN sys.indexes I
ON T.object_id = I.object_id
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) F
On F.object_id=i.object_id
WHERE T.type = '
'U''
ORDER BY T.Name
'

)

SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag

TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

WHILE (@min_id<=@max_id)
BEGIN

SELECT
@tab_name=table_name,
@schema_name=schema_name,
@ind_name=index_name ,
@frag=frag ,
@pages=pages
FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
BEGIN
IF (@frag>=30 AND @pages>1000)
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',GETDATE())
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',GETDATE())
END CATCH
END

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
ELSE
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',GETDATE())
END
END
ELSE
BEGIN

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',GETDATE())
END

SET @min_id=@min_id+1
END
DROP TABLE #tempfrag

INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history

END

SSIS Destination Locking

See Top 10 SQL Server Integration Services Best Practicesfor some useful information on making SSIS work better for you.

Found this one when trying to work out the best values for preventing lock escalation in our SQL server when inserting lots of records into live environment. Setting small commit size of <5000 is recommended.

This is found in the OLE DB Destination Editor window.

Visual Studio Microsoft Image Library

The Visual Studio Image Library contains application images that appear in Microsoft Visual Studio, Microsoft Windows, the Office system and other Microsoft software.

http://www.microsoft.com/en-us/download/details.aspx?id=35825

Useful for winforms developers and avoids having to dig deep into your visual studio install for the previously available images…

Partner Transaction Manager disabled remote eConnect Dynamics GP

"Partner Transaction Manager has disabled its support for remote/network transactions" error message in Microsoft Great Plains eConnect

Specifically talking about modern operating systems such as 2008/2012/Win7/Win8.

Specifically talking about eConnect version 11, for GP2010

Chill runs through your spine as you install eConnect and then try to run an eConnect document through from another machine.

This message is unusually accurate in describing the problem. eConnect uses DCOM to execute against a remote server and client using transactions, so they can be rolled back if things go wrong.

The DCOM layer introduces its own layer of security into the pot that has to be configured. Normally if eConnect is installed and an application or service uses it from the same machine to which it was installed, then everything works as expected. Move that application or service to another machine, then the above message is returned as an exception.

The following document describes solving this problem on windows 2003 server "Partner Transaction Manager has disabled its support for remote/network transactions" error message

On server 2012 things are different and this document failed. The following steps solved this issue for a particular install, I don’t know which step or combination of steps were actually required. I need to go back and undo one at a time to find that out

Go to Control Panel>>Administrative Tools>Component Services or type “com” into the windows server search box, select Component Services.

In the past there was, certainly for eConnect9 items under Computers>>MyComputer, for “eConnect9”. There no longer is any evidence of eConnect in the browser, I’m assuming the visibility flag on the COM application build have been set to hide from browsing.

Right click on the MyComputer node, select properties.

image

Select each in turn of the Edit buttons (four of them) on the COM Security Tab. Add to these security items, a group to which the user running the service (or app) belongs to. To that newly added group then tick allow for Remote Access. Follow that pattern through all the four button permission boxes.

image

Nothing else was changed on the other tabs.

RESTART Distributed Com Windows Service.

image

RESTART SQL Server may be required according to some documents, only do this if you have to.

This is where it gets odd. In 2003, the MSDTC tab had a Security Configuration Button, by clicking on that and allowing in abound and outbound and remote clients everything would come to life, on server 2012 this is missing. Argh.

Server 2003 with DTC security

image

Server 2012, no DTC settings

image

At 3am I was getting irritated by this!

Registry hack

** Please see notes at bottom as there may be a more systemic way to to do this!

Usual note: Only edit the registry if you know what you are doing!

The following document had the clue,

Enable Network Access Securely for MS DTC

image

So by changing the regstry settings it is possible to do what the old screen was doing.

So change the

Security\NetworkDtcAccess

Security\NetworkDtcAccessTransactions

Security\NetworkDtcAccessInbound

Security\NetworkDtcAccess

Security\ NetworkDtcAccessTransactions

Security\ NetworkDtcAccessOutbound

Security\NetworkDtcAccessClients

All above should be set to “1”

image

Restart the Distributed Transaction Service and SQL server if required and now eConnect will work with the application from a remote machine and I could get to bed!

Security Configuration Wizard

The server 2012 security configuration wizard may be the way that this is now supposed to be edited, rather than using the registry. In here there is the option to switch on Remote COM+.

image

image

Time to start reading up on the server 2012 administration, as it impacts us developers!