Hoe to reduce size of the database in CRM

Všechny reakce

• 3. května 2012 13:07

  Přispěvatel

   

   
  

  0

  You can reclaim allocated by unused space using SHRINKDATABASE (http://msdn.microsoft.com/en-us/library/ms189035.aspx) but obviously it won't reduce the size beyond what is physically used by data. 

  If you don't know where your data is being used - you can run a report to show the size per table. IF you find that there is a lot of space used by the AsyncOperations Table you will want to look at http://support.microsoft.com/kb/968520.

  hth,

  Scott

  ---

  Scott Durow
  Read my blog: www.develop1.net/public
  If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

  
  

  • Upravený Scott DurowMicrosoft Community Contributor, Editor 3. května 2012 13:08
  •  
  • Reagovat
  • Citovat

   
• 3. května 2012 13:12

   

   
  

  0

  Hi Scott;

  thanks for you r reply . belwo are my tables which uses more space.. is there any chance that i can reduce these tables

  Thanks

  Table_Name	Rows	Reserved_KB	Data_KB	index_Size_KB	Unused_KB
  EmailHashBase	1791953	458816 KB	125424 KB	273288 KB	60104 KB
  ActivityPartyBase	1571407	959488 KB	349752 KB	601792 KB	7944 KB

  ---

  Pradnya07

  • Reagovat
  • Citovat

   
• 3. května 2012 13:16

   

   
  

  1

  It depends where this unused space is. Based on the table above I would say this space is in the data file of the database.
  You can shrink this using SQL Management Studio. Just right click the database and select Tasks > Shrink > Files. In the dialog select the option 'Reorganize pages ...' and click OK. You can also shrink the log files.

  You can also use a maintenance plan to reorganize the database and release unused space. In the maintance plan wizard select the rebuild index and shrink database tasks (first rebuild than shrink).

  It is important to note that there should always be available space (unused) in the database so do not release all free space. There should be some free space because updates to the database will need some space to write this data to, using the default settings the files will automatically be increased in size. In your case you have 3Gb available in a 96Gb database, this is not that much. I would not think it is really necessary to shrink the database.

  ---

  Patrick Verbeeten
  www.patrickverbeeten.com
  www.wavextend.com

  • Reagovat
  • Citovat

   
• 3. května 2012 13:41

   

   
  

  0

  Thanks Patrick for your reply

  do i need to do reduce the index size or s it right

  Thanks

  ---

  Pradnya07

  • Reagovat
  • Citovat

   
• 3. května 2012 13:56

  Přispěvatel

   

   
  

  0

  Hi,

  If you run SHRINKDATABASE, you should reclaim some of this reserved space yes.

  Scott

  ---

  Scott Durow
  Read my blog: www.develop1.net/public
  If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

  • Reagovat
  • Citovat

   
• 3. května 2012 14:04

   

   
  

  0
  what is database optimisation ??? can i do something like that

  ---

  Pradnya07

  • Reagovat
  • Citovat

   
• 3. května 2012 23:17

   

   
  

  0

  As has already been stated, the percentage of unallocated space in your database is very small.  As has also been stated, a certain percentage of unallocated space in an Enterprise level relational DBMS is as it should be - it allows for growth without excessive re-allocation, which is a heavy performance burden.

  Given that the "issue" with your database is a large quantity of data, your only real option is to archive and/or cull.

  Aside from the issue of disk usage; if you are not already regularly maintaining your database with Index Reorganisation and/or Rebuilding (Database Optimisation), you should be doing so.  Indexes become fragmented over time (within the CRM, many of them become severely fragmented); this can impact performance of queries that use those indexes, to the point where they do more harm than good.

  ---

  --pogo (pat) @ pogo69.wordpress.com

  • Reagovat
  • Citovat

   
• 10. května 2012 22:34

   

   
  

  1

  Hi,

  Looking at your index size being high for the tables, I suggest running the stored procedure p_ReindexAll present in the stored procedures of the organization database. This should help with defragmenting the indexes and thereby also controlling their sizes.

  Thanks,

  Adi.

  • Reagovat
  • Citovat

   
• 11. května 2012 9:21

   

   
  

  0

  Hi Aditya;

  Thanks for your reply

  Below is my database index size after running the procedure you mention

  Didnt find much difference

  Do you knwo tehreason why?

  Thanks

  P

  Database Name	Database Size	Unallocated Space
  MSCRM	103559.00 MB	6327.57 MB
  Reserved	Data	Index Size	Unused
  90537144 KB	66033328 KB	6135464 KB	18368352 KB

  ---

  Pradnya07

  • Reagovat
  • Citovat

   
• 24. května 2012 22:15

   

   
  

  0

  Hi,

  Can you please try to run Shrink Database now as was suggested above. I see unused space to have grown to 18GB and data to have shrunk to 66GB. It should give you savings now.

  Thanks,

  Adi.

  • Reagovat
  • Citovat