For any database driven application, it is very important that the database must be optimized to the best so as to deliver higher performance. As the data volume in database increases, load on the system also increases which affects the performance on the system which in turn affects the client’s business. For business application like Sage CRM, this must be paid attention on.
New Stuff: Migration of Act! Groups in Sage CRM
Recently, we got a support email from one of our client saying that CRM is slow and users are not able to work on it. We quickly made some preliminary checks and everything seemed proper, no SQL error, nothing suspicious in System logs….Huh..!!! But we noticed that drive of CRM instance directory was almost full. We investigated some folders which generally consume more space of the drive and we got to know that database size was considerably large compared to the volume of data. So decided to check size of each table.
Now, how to get size of each table? Below is the SQL Script to get size of each table in database.
CREATE PROCEDURE GetAllTableSizes
AS
/*
   Obtains space used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)   –For storing values in the cursor
 
–Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N’IsUserTable’) = 1
FOR READ ONLY
 
–A procedure level temp table to store the results
CREATE TABLE #TempTable
(
   tableName varchar(100),
   numberofRows varchar(100),
   reservedSize varchar(50),
   dataSize varchar(50),
   indexSize varchar(50),
   unusedSize varchar(50)
)
 
–Open the cursor
OPEN tableCursor
 
–Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
–Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
   –Dump the results of the sp_spaceused query to the temp table
   INSERT #TempTable
       EXEC sp_spaceused@TableName
 
   –Get the next table name
   FETCH NEXT FROM tableCursor INTO @TableName
END
 
–Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
–Select all records so we can use the reults
SELECT *
FROM #TempTable order by tableName
 
–Final cleanup!
DROP TABLE #TempTable
 
GO
Execute above sript against CRM database. Executing this procedure will return output as shown below

When we executed above procedure against the database, we found that size of TableScriptLogs table was much larger. This table is nothing but the Table Level Scripts logs. Looking at the logs we found an issue at TLS level and surprisingly neither any exception were thrown on the screen nor any screen crash happened but log was getting written each time which resulted into larger data size of table. Truncating this table resolved the issue being faced. We resolved that issue too…:)
This is how we can use above procedure to troubleshoot database level issue.
Also Read:
1) Avoid scrolling with Collapse Screen Feature in Sage CRM7.2
2) CRM database auto-backup on SQL express Editions
3) Sage 100 user mapping with Sage CRM User
4) Additional Information in Entity Top Content Screens – Sage CRM v7.2
5) User specific data on Interactive dashboards