locked
Shrink DB? I am setting up a DB maintenance plan. RRS feed

  • Question

  • Just started using CRM, I'm the primary Administrator - I am wondering if I should use SHRINK as part of my maintenance plan? I have heard that it causes problems?

    Also, anyone have any DB maintenance tips?

    Thursday, November 27, 2014 1:35 PM

Answers

  • Hi ,

    Please go through below link for SQL bad practices so that you can have better idea about daily maintenance activity.

    You should consider shrinking only when the following conditions are met:

    • You desperately need the space and can’t add disk space to the system
    • You deleted a lot of data (or grew the data file too much) and you don’t expect the data files to grow back. A good example would be to shrink files which are on a filegroup that will become read-only, such as with date-based partitions.
    • This is a manual operation and the shrink is not part of a recurrent maintenance task
    • You first tried SHRINKFILE with the TRUNCATEONLY argument. The operation won’t induce fragmentation and the SHRINKFILE will be very fast.

    http://www.sqlbadpractices.com/should-you-shrink-your-database-in-your-maintenance-plan/

    Regards

    Soni K


    • Edited by Soni K Thursday, November 27, 2014 2:53 PM
    • Marked as answer by tahirx21 Thursday, November 27, 2014 5:21 PM
    Thursday, November 27, 2014 2:53 PM
  • In small organisations and deployments you can probably get away with it once every weekend, on a Sunday night, but even then I would not recommend it. As the others have said DB shrink is one of those horrible things that can drastically degrade the performance of CRM. In any case, I would do a CRM baseline benchmark using SQL profiler, Activity Monitor or PerfMon (probably do all) then get a report for before and after. You'll soon realize if there are any benefits to being had by shrinking. 
    • Marked as answer by tahirx21 Thursday, November 27, 2014 5:21 PM
    Thursday, November 27, 2014 5:11 PM

All replies

  • shrink and reorg once in a week is good or also good manually after depoying solutions. Never caused any problems sinice crm 4..

    one thing what i recommend is to create indexes of high cost queries also on sharepoint sql if youre using for documents.


    gruss Daniel Ovadia MBSS - Microsoft Dynamics CRM MCNPS

    Thursday, November 27, 2014 2:11 PM
  • Hi ,

    Please go through below link for SQL bad practices so that you can have better idea about daily maintenance activity.

    You should consider shrinking only when the following conditions are met:

    • You desperately need the space and can’t add disk space to the system
    • You deleted a lot of data (or grew the data file too much) and you don’t expect the data files to grow back. A good example would be to shrink files which are on a filegroup that will become read-only, such as with date-based partitions.
    • This is a manual operation and the shrink is not part of a recurrent maintenance task
    • You first tried SHRINKFILE with the TRUNCATEONLY argument. The operation won’t induce fragmentation and the SHRINKFILE will be very fast.

    http://www.sqlbadpractices.com/should-you-shrink-your-database-in-your-maintenance-plan/

    Regards

    Soni K


    • Edited by Soni K Thursday, November 27, 2014 2:53 PM
    • Marked as answer by tahirx21 Thursday, November 27, 2014 5:21 PM
    Thursday, November 27, 2014 2:53 PM
  • Under normal operations, I wouldn't expect the space requirements of the CRM database to go down, so there seems little point in a scheduled Shrink.

    However, importing a large solution can cause a lot of transaction log growth (as a solution is mostly imported as one transaction), so you can end up with excess space in the transaction log. So, you might consider a shrink of the log file after any such imports 


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, November 27, 2014 4:18 PM
    Moderator
  • In small organisations and deployments you can probably get away with it once every weekend, on a Sunday night, but even then I would not recommend it. As the others have said DB shrink is one of those horrible things that can drastically degrade the performance of CRM. In any case, I would do a CRM baseline benchmark using SQL profiler, Activity Monitor or PerfMon (probably do all) then get a report for before and after. You'll soon realize if there are any benefits to being had by shrinking. 
    • Marked as answer by tahirx21 Thursday, November 27, 2014 5:21 PM
    Thursday, November 27, 2014 5:11 PM
  • Thanks for your replies everyone. We're a Mid-sized organisation so our DB grows quite large fairly quickly. I'm going to try and do a benchmark as Syed suggested of the current system performance before and after a shrink. I'll report back on my findings just in case it helps anyone out in the future.
    Thursday, November 27, 2014 5:21 PM