locked
CRM log growth rapidly RRS feed

  • Question

  • Hi all,

    I have a problem with crm database. every day database log file size for crm (organization_CRM_Log.ldf) increase rapidly and made out of space error in database server. Also i must shrink it everyday. previously the log file size was only 5GB and then its increased to 74GB and then i shrink it to 8mb, but it increased again to 65GB in a day. Does anyone know how to fix this or source of this issue?

    thank you

    Friday, September 24, 2010 1:05 AM

Answers

  • The particular query is part of the CrmDeletionProcess, which runs automatically every 24 hours by default. You can change the schedule using the Scale Group Job Editor

    Note that this process removes records that have already been deleted by CRM users, so telling them to change their behaviour won't help.

    I expect that the problem is that you have so much data waiting to be deleted that the SQL will always fail, but keeps trying each time. You've probably got into the nasty situation whereby the fraction of data is sufficiently large that CRM decides an exclusive table lock is the most effective way to delete the records, but this can only work if nothing else is accessing the database. Your best option would be schedule a period of time (say a weekend) whereby CRM is not available to users (I'd block external access to IIS), also stop the Email Router, and unpublish any workflows that reference activities (to avoid them blocking the deletion process), and schedule the CrmDeletionProcess to run at this time


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Edited by DavidJennawayMVP, Moderator Wednesday, September 29, 2010 11:51 AM I had incorrectly suggested stopping the CRM Async Service, but this needs to be running to run the Deletion Process
    • Marked as answer by Donna EdwardsMVP Wednesday, September 29, 2010 1:00 PM
    Tuesday, September 28, 2010 3:54 PM
    Moderator

All replies

  • If you want, you can setup a scheduled task and run this every few hours:

    save the below script to: shrinklog.sql

    USE [ORG_MSCRM]
    GO
    DBCC SHRINKFILE(ORG_MSCRM_log, 1)
    BACKUP LOG ORG_MSCRM WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(ORG_MSCRM_log, 1)
    GO
    

    put this into a batch file

    sqlcmd -S . -i "C:\shrinklog.sql"

    http://mscrmblog.net
    Microsoft Certified Business Management Solutions Specialist
    Microsoft Certified CRM Developer
    Friday, September 24, 2010 1:34 AM
  • The source is most likely your recovery model.  This is not unique to CRM if this is the root cause.  Please see this article about SQL Recovery methods and managing the related log files.

    Regards, Donna

    • Proposed as answer by Donna EdwardsMVP Friday, September 24, 2010 1:47 AM
    • Marked as answer by Jim Glass Jr Monday, September 27, 2010 7:06 PM
    • Unmarked as answer by Well J Tuesday, September 28, 2010 3:38 AM
    Friday, September 24, 2010 1:47 AM
  • Hi all, thank you for your reply. ibbz101, I could not use your solution. since it not fix the main issue.

    I think i will try Donna suggestion. It was okay last week. but after I delete a large number of data, i always got this error. Is it have connection with it?

    Friday, September 24, 2010 2:18 AM
  • why don't you just detach the DB obvioulsy at some idle time (after office hours) delete the ldf file (make a  copy) and attach mdf file again.
    Saturday, September 25, 2010 7:07 AM
  • Hello,

    Witch rollup have you installed? I read something like this on rollup 8.

    []´s


    Carlos Amorim Junior
    "Já ajudou a comunidade hoje?"
    http://www.dynamicscrm.com.br
    Sunday, September 26, 2010 2:45 PM
  • I have rollup 11 installed. I found the root of this error. database log increased everyday because there is an SQL query that executed multiple times on every afternoon. but the query always got failed. below is the query:

    delete from ActivityPointerBase where DeletionStateCode = 2

    one single delete statement deleting nearly 20 gigabytes of data in one statement. This kind of statement will not work.  How to stop it without killing this process every night?

    There is no bulk deletion job in my CRM application.

    Monday, September 27, 2010 2:57 AM
  • Why do you have 20 gigs of data to delete from your crm database every night? Generally speaking, the deletion query generally fails because a record or records were removed manually from the database (unsupported) which causes the query to fail on certain records because dependent records are not available for deletion.

    Have a look at this post for the solution


    Regards, Donna

    Monday, September 27, 2010 12:57 PM
  • Have you looked at user behavior?

    If none of these other items work for you, I would look at offline clients coming onine in the monring. It is possible you have something wrong with one users behavior.

    • Marked as answer by Jim Glass Jr Monday, September 27, 2010 7:06 PM
    • Unmarked as answer by Well J Tuesday, September 28, 2010 3:36 AM
    Monday, September 27, 2010 1:13 PM
  • When I got this error, i sent a notification to users to prevent deletion activity in CRM. Especially for large number of data. Also there was a deletion for some quick campaign. But it was deleted in 17 Sept and after that no deletion activity executed, but till now the CRM log and tempdb file still increased every day. Is it because user who delete quick campaign is not an administrator?
    Tuesday, September 28, 2010 3:36 AM
  • Let's circle back, generally speaking, sql log file growth is due to the recovery mode and not deletion of records.  You can have a look at this KB for more information or read more about recovery models in technet.  You will find lots of information about how to manage your SQL log file growth.

    Regards, Donna

    Tuesday, September 28, 2010 1:17 PM
  • The particular query is part of the CrmDeletionProcess, which runs automatically every 24 hours by default. You can change the schedule using the Scale Group Job Editor

    Note that this process removes records that have already been deleted by CRM users, so telling them to change their behaviour won't help.

    I expect that the problem is that you have so much data waiting to be deleted that the SQL will always fail, but keeps trying each time. You've probably got into the nasty situation whereby the fraction of data is sufficiently large that CRM decides an exclusive table lock is the most effective way to delete the records, but this can only work if nothing else is accessing the database. Your best option would be schedule a period of time (say a weekend) whereby CRM is not available to users (I'd block external access to IIS), also stop the Email Router, and unpublish any workflows that reference activities (to avoid them blocking the deletion process), and schedule the CrmDeletionProcess to run at this time


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Edited by DavidJennawayMVP, Moderator Wednesday, September 29, 2010 11:51 AM I had incorrectly suggested stopping the CRM Async Service, but this needs to be running to run the Deletion Process
    • Marked as answer by Donna EdwardsMVP Wednesday, September 29, 2010 1:00 PM
    Tuesday, September 28, 2010 3:54 PM
    Moderator
  • Hi David,

    I'm trying to understand how the failing deletion job will create the following environment issue:

    "CRM log and tempdb file growing substantially each day"

    It seems that there might be two issues going on, the failing deletion job & the growing log file which I think is related to the recovery model used but I could wrong.  The original question posed was to understand why his / her log files are growing by 65 gbs a day and it seems that 'Well J' thinks this is related to the deletion job.

    Maybe you can shed some light on this for me.

     


    Regards, Donna

    Tuesday, September 28, 2010 4:11 PM
  • Hi David and Donna,

    Sorry with my English, I could not gave you clear explanation. Yes the data already deleted by users and they was using advanced find to delete the data. There are so much data awaiting to be deleted but SQL always get failed and retry it every time. I will try David suggestion.

    thank you all.

    regards,

    Well J

    Wednesday, September 29, 2010 3:39 AM
  • Well J. If you try my suggestion, consider the following:

    1. I didn't previously notice the reference to tempdb growth. If tempdb is also growing significantly, then you may have Snapshot Isolation mode turned on in the MSCRM database. It's difficult to be certain, but I would expect my suggestion is more likely to work if Snapshot Isolation is turned off (see http://www.google.co.uk/url?sa=t&source=web&cd=2&ved=0CCEQFjAB&url=http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Ftcbchxcb(v%3Dvs.80).aspx&ei=CiKjTOr_BcK4jAfpgoipAw&usg=AFQjCNFYesLNooEyzGC0cOgdrHHQtRZygw )
    2. I was wrong to suggest that you stop the CRM Async Service, as it is this service that runs the Deletion Process. However, if possible, try and Unpublish all workflows, especially any that use activities in any way (I'll edit my earlier response to reflect this)

    Donna, hopefully I'll have time to write a fuller response in a blog post, but the main points are:

    1. Based on the information given, I expect the vast majority of the writes to the transaction log are caused by the CRM Deletion Service, and the daily recurrence is due to the job failing each time, and trying again. My suggestion is to try and fix this
    2. The recovery model is one of the ways to control what happens to the transaction log. Setting the recovery model to Simple will keep the log file smaller through periodic truncation, but you'll still get 65 GBs if disk writes during the day, it's just that they will then get deleted periodically. So, you'll save on overall disk space usage, but I wouldn't like to have that volume of disk activity. The other way to control the log size is by periodic log backups; I much prefer this as this gives more recovery options (e.g. Point in Time restore), but it'll be difficult to manage to storage of backups of this size

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Wednesday, September 29, 2010 11:48 AM
    Moderator
  • Thanks David, I appreciate your time and response.

    Regards, Donna

    Wednesday, September 29, 2010 1:01 PM
  • Thank you David and Donna,

    Currently we still struggling with free space in database server. Our CRM database already reach 120Gb, and database growth almost 2GB a week. We want to delete unused data in CRM. I will try David suggestion on weekend. Since we could not turn off CRM application on office day.

    For temporary solution i always kill delete process in SQL server every afternoon, so tempdb and CRM log file size wouldn't be increased again.

    thank you and regards,

    Well J

    Thursday, September 30, 2010 1:16 AM