Reducing database size

Member since: 2011

I am generating and deleting a lot of pages programatically. So the EPi databse is growing big (Is that because of alot of log data?). And what i can do to reduce the size of the databse? Like what tables that i can use to remove old log data that is not needed?


Thanks.

#65802 Feb 11, 2013 9:12
  • Member since: 2007

    You can "shrink" the database with SQL managment stuido also make sure you do not have to much loggin enabled for SQL. Rightcklick on you db in sqlmanagement studio, choose task, then shrink database.

    #65803 Feb 11, 2013 10:01
  • Member since: 2005

    Also check siteSettings and see what value you have for uiMaxVersions. If it's set to 0 you should set it to a number larger (for example 9 or 14) than 0 and also run a script to delete the possibly massive amount of older versions.

    #65804 Feb 11, 2013 10:22
  • Member since: 2008

    Also, if you're using Page Type Builder, check which version. Before v2.0, there was a bug which caused the tblBigTableReference to be flooded with junk. See http://pagetypebuilder.codeplex.com/workitem/8750

    In some cases we had millions of unnecessary rows, resulting of several gigabytes of junk.

    To see if this is relevant for you:

    - Check the PTB version. If below 2.0, you're affected.

    - See if this query returns a massive amount of rows:
    SELECT DISTINCT pkId FROM tblBigTableReference where PropertyName = 'Buttons'

     

    #65914 Edited, Feb 14, 2013 6:12
  • Shrinking database might not be a good idea with MSSQL. Shrinkin database causes data fragmentation and lower performance.

    http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/

    Do not use shrink unless you are running out of disk space.

    #65934 Feb 15, 2013 10:04
  • Member since: 2005

    Published a blog post about how to analyze DB size in more detail: http://krompaco.nu/2013/02/analyzing-sql-server-database-size/

    #66049 Feb 19, 2013 0:35
  • Member since: 2011

    Thanks Johan, it was the tblChangeLog that has the millions of records.

    Also Thanks Arild, Yes i am using PTB but v2.0

    Now i deleted the unused log but the database size still the same and all the methods say that you should shrink the database to claim the free space? Anyone have an idea of another way to claim the space to avoid the data fragmentation that maybe caused by shrinking?

     

    #66225 Feb 25, 2013 10:29
  • Member since: 2005

    Great! Note the built-in Scheduled Job named "Change Log Auto Truncate" that you should make sure is running regularly.

    #66226 Feb 25, 2013 11:12
This thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.
Please login to post a reply