Reducing database size

Youssef Lamaei

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

Eric

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

Arild Henrichsen

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

Matti Mertojoki

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

Youssef Lamaei

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