Noticias del Mundo - News of the World: databases

Mostrando entradas con la etiqueta databases. Mostrar todas las entradas

CRM 4.0, Worflowlogbase y AsynOperationbase very large

11:55 |

Publicado por Killiam |

Editar entrada

I am really sure, if you are working with Microsoft CRM 4.0 for few time, or you are fighting with it... and you open the database, with a name similar to Your-ORG_MSCRM, and you check this tables:

AsyncOperationBase
WorkflowLogBase

Look the properties, number of lines and also MB of these tables, sure you will have a fright.... I am sure they have a lot of lines, and also an important size in MB, I bet they do... The reasons for this:

The CRM 4.0 by default doesn´t delete the finished workflows, due to this in the Workflowlogbase table, a lot of unnecessary information is stored.

The Asyncoperationbase table, is used to save information of Microsoft CRM Asynchronous Processing Service (MSCRMAsyncService), which lead again in a lot of unnecessary information is stored.

The steps we will follow to solve the problem are the following:

The first step is ESSENTIAL, make a complete backup of CRM DATABASES

We will modify or add some keys in the register, to avoid finished Workflows to be stored in the database

We will run some scripts in the database that will empty Worflowlogbase and Asyncoperationbase tables

Well, lets work...

1-Backup

Well..., I think all of you Know how to make a SQLSERVER backup.... so I will jump this step... If you don´t know how to make a backup... I am not sure if you should continue readinf this article.... (well anyway if you google you will find how to make a backup easyly)

2-Windows Register Modification:

Go to this branch of the register Windows HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM

Check if the following key exists AsyncRemoveCompletedWorkflows, in case that exist change the value of this key to 1

In the case that this value doesn´t exist, we will create a DWORD key with the next name AsyncRemoveCompletedWorkflows and set the value of this register in 1

We wil execute an IISRESET in a MSDOS window, this will restart IIS services.

¿What are we doing setting the value of this register in 1 or adding the key AsyncRemoveCompletedWorkflows in the Windows register? By Default CRM 4.0 save in the databse all workflows, even those which had finished, for the majority of us this information is unusable, setting this register AsyncRemoveCompletedWorkflows to 1 no more finished workflows will be stored in the CRM database.

3-Running scripts to empty the lines of the Workflowlogbase and Asyncoperationbase.

Now in the CRM 4.0, no more finished workflows will be stored. But... how we should proceed to delete the information of those finished workflows that has been saved previously in Workflowlobase and Asyncoperationbase tables? , sure that there are hundreds, thousands, or probably millions of lines... don´t worry we have a solution for this... Remember that these scripts must be executed in the database which name is similar to this Your-ORG_MSCRM

Stop "Microsoft CRM Asynchronous Processing Service"

Second step we will create indexes to optimize the script execution:

CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
GO

CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])

CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase] (AsyncOperationID)
GO

Third step, update and rebuild the following indexes:

ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

Fourth step, update all statystics involved in the queries...

UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

Last step, this is the script that will delete the unusable information from this two tables:

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

declare @DeleteRowCount int

Select @DeleteRowCount = 2000

declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)

declare @continue int, @rowCount int

select @continue = 1

while (@continue = 1)

begin

begin tran

insert into @DeletedAsyncRowsTable(AsyncOperationId)

Select top (@DeleteRowCount) AsyncOperationId

from AsyncOperationBase

where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)

Select @rowCount = 0

Select @rowCount = count(*) from @DeletedAsyncRowsTable

select @continue = case when @rowCount <= 0 then 0 else 1 end

if (@continue = 1)

begin

delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d

where W.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d

where B.AsyncOperationId = d.AsyncOperationId

delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

where A.AsyncOperationId = d.AsyncOperationId

delete @DeletedAsyncRowsTable

end

commit

end

--Drop the Index on AsyncOperationBase

DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

Una vez hecho esto ir a mirar el tamaño de estas dos tablas y veréis el resultado.....

Comentaros:

It is essential, follow the previous steps, before running the delete script. The create indixes, update statystics etc... if not, it will take a hundred years before finish the delete of the line... (I am talking for my own exprience, had to stop the script after more than some hours running)

Do Not execute the scripts during peak hours.

That´s it.... hurry up executing scripts, and deleting unuseful information from your CRM database

Source:

Etiquetas: databases, Microsoft CRM, optimize, T-SQL, technical |

1 comentarios

How to export data from a Database to Excel?

11:17 |

Publicado por Killiam |

Editar entrada

In this entry I will explain how to export data from a Database (in this case Oracle) using Excel (version 2007). The point is that later We can import this information easily in other database, and Excel files allow us to modify it very simply

Well lets get to work, the procedure is very easy,,,,

1-First of all we should configure a ODBC connection against the Database, in our cas ORACLE, to do this we should go, to Administrative tools -> Data Sources (ODBC)

2-In the ODBC, we will check that we have installed the driver we will use to connect to the Database, in this case we will use driver named Oracle in ora92 (we have installed previously)

3- Then we will open Excel (the Excel version we use is 2007), and we will set and we will configure and External Origin of Data. To do this, go to the tab Data -> From Other Sources -> From Microsoft Query, as we can see in the screenshot.

4- In the next screen we will select New Data Origin (the part marked with the red box)

5-Now we have to type the name of the connection we are setting up (the one you want), and the driver of the connection we will use, as we said before Oracle in Ora92

6-And Now click on the connect button, and a new windows will appear, and we will be asked to type, SERVICE of the Oracle database, a USER and PASSWORD. Remenber that this user may have permissions to do selects to the database from which we want to export data

7- Now a new window will appear, in it we will be able to see the tables of our database, we will select the table from which we want to export the data

8- If we click in the table we will see the columns of it

9 - And it we click on the arrow in the red box, we will add all the columns to the select.

10- In the next screen, we will select the option View data or Edit Query in Microsoft query

11- Once we have done this, Microsoft Query will be opened, in which we will able to see the data of the table, and all the columns of it.... and if now we click on the button in the red box , where we can read SQL

12- Now, is the most interesting part, a window will be open, and we will see the select that is being executed, and modify it for our liking. In the screenshot yo will see that this is a good old SELECT. IMPORTANT REMEMBER THAT AS WE ARE WORKING AGAINST A ORACLE DATABASE THE T-SQL, MUST BE ORACLE T-SQL

13- Once we have modified the select to our needs, and we need to be returned the data to Excel, just click File -> Return data to Microsoft Excel

14- Excel will be opened with the next windows, we will click accept.

15- When we have finished all these steps, Excel will be opened, and it will show all the data from the select we have done.

And Folks, have the information in Excel can give use a lot of possibilities to work with this data, whether it is, import this file directly to other database , conver this file into an other kind of file.... in sum everything we want...

Have fun exporting you data.... :-D

Etiquetas: BBDD, databases, Excel, Microsoft Excel, Microsoft Query, Oracle, T-SQL, technical |

0 comentarios

Mejorar rendimiento SQLSERVER // Improve SQLSERVER performance

13:36 |

Publicado por Killiam |

Editar entrada

Buenos vamos a ello, la primera entrada de verdad. Como ya dije hare algunos artículos para la gente más técnica, y que me imagino a alguno ayudaran. Son de esas cosas que a uno le cuesta encontrar por internet, y que son bastante útiles.

En este articulo voy a copiar un script que ayudará a toda aquella gente que es administrador de sqlserver o que ha desarollado aplicaciones para SQLSERVER. Este script en T-SQL hace los siguiente:

Busca todos los indices de la BBDD en la que se ejecute el script
Mira el estado de fragmentación de los Indices
Si el indice está fragmentado menos o igual de un 10% no hace nada
Si el indice en cuestión está fragmentado de un 10% - 30% lo reindexa
Si el indice tiene un estado de fragmentación mayor a un 30% lo reconstruye

Una vez ejecutado el script notareis un mejora importante en el rendmiento de vuestras bases de datos y vuestras aplicaciones.

(El script es la parte en rojo)

--------------------------------------------

Well lets go, honestly this is the first article. As I told, I will blog some technical entries, that I suppose that will help somebody (or at least this is my intention). Things that are difficult to find in the Web, but they are really useful.

This article is about a T-SQL script for SQLSERVER that will help database administrator, or even the people who have an application running on a SQLSERVER database. This T-SQL scripts do the following:

Find all the indexes on the selected database
Check the state of fragmentation of the indexes
If the index is fragmented equal o below 10% makes nothing
It the fragmentation of the index is between 10% - 30% it will be reindexed
If the fragmentation of the index is above 30% it will be rebuild

Once you do this you will notice a dramatically improvement in the database and your application.

(The script is the part in RED)

-----------------------------------------------

USE --DATABASENAME

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS <>

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;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag <>

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

Etiquetas: BBDD, databases, microsoft, performance, rendimiento, sqlserver |

2 comentarios

Noticias del Mundo - News of the World

Archivo del blog

Entradas populares

CRM 4.0, Worflowlogbase y AsynOperationbase very large

How to export data from a Database to Excel?

Mejorar rendimiento SQLSERVER // Improve SQLSERVER performance