Thursday, November 24, 2016

MSCRM and the ever growing database

Not for the first time I've come across a SQL server with a full disk.
The culprit database was a MSCRM organization database which had grow to ridiculous size.
TLDR version: activity logging in CRM will generate a lot of data and consume your disk space.

Troubleshooting and fixing your oversize SQL database looks something like:

Open Dynamics CRM Deployment Manager.
Right click on your Organization and click Disable.  This will prevent access to the Org and stop it from accessing the database while you fix it.
Consider a SQL restart to clear any connections if you can.

Set Database to simple mode and flush old logs
USE ORGNAME_MSCRM
GO
ALTER DATABASE ORGNAME_MSCRM
SET RECOVERY SIMPLE; 
GO 
DBCC SHRINKFILE (mscrm_log, truncateonly)
GO 

Find the oversize table
USE {YOURDATABASE}
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 

Empty the oversized table (assuming your table isn't one you like very much)
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
 BEGIN
  BEGIN TRANSACTION
   DELETE TOP (10000) ActivityPointerBase 
  COMMIT TRANSACTION
CHECKPOINT
SET @Deleted_Rows = @@ROWCOUNT;
END

Set Database back to normal
USE ORGNAME_MSCRM
GO
ALTER DATBASE ORGNAME_MSCRM
SET RECOVERY FULL; 
GO 

Depending on the size this may take a very long time.  In my case I went a step further and switched the database to Single User access while the above was completed.  YMMV


If you encounter this and have success or problems resolving the issue please leave a comment and let me know.
I'd be curious what others have done to either fix or prevent this for occurring.



No comments:

Post a Comment