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?
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.
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.
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'
Shrinking database might not be a good idea with MSSQL. Shrinkin database causes data fragmentation and lower performance.
Do not use shrink unless you are running out of disk space.
Published a blog post about how to analyze DB size in more detail: http://krompaco.nu/2013/02/analyzing-sql-server-database-size/
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?
Great! Note the built-in Scheduled Job named "Change Log Auto Truncate" that you should make sure is running regularly.