Reducing database size

Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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
Vote:
 

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.