How to reduce database size efficiently so it does not effect on site performance. How to remove more than 10 version of all pages. I tried to remove old version of pages but it is taking alot of time to delete because there are million of records exsit. Please Suggest me best way to do it. Thanks !
I don't know the "best" way but I would suggest you to turn on the UIMaxVersions settings. It's the maximum number of versions allowed per page. UIMaxVersions =3 seems to be a good start. And when you save a page, it will delete old versions if total number is > 3. So it'll be, incrementally reduce your database size.
but there are more than 50,000 version exsit for some pages. How can i remove those ? I used the Schedule Job but it is taking 10 hours time to execute the query but can not finished its execution and no rows effected. Can you provide me the script ???
In that case I would suggest to do a dummy edit-save on the pages with more than 50.000 versions (with UIMaxVersions enabled). The deletion will run in database only so I would expect the performance will be much faster than loading then delete old versions.
How much time it will take to delete the versions ?
I don't know (without knowing many factors such as your site, your database, your hardware,...), but as I said, the deletion was on DB entirely so theoretically it can complete in matter of seconds for deleting 50.000 versions.
That make sense. How can i do empty recycle bin. I did it from Episerver Edit Mode but it does not deleted. then i used (EXEC editDeleteChilds @PageID = 2, @ForceDelete = 1) query to delete the childs. but still its existed under recyclebin folder. Can you tell me why it is happening ?
Can i do dummy edit-save on the pages programatically instead of visting each page & edit-save it?
I haven't tried it myself but you can simply update the Changed property of PageData and save it? I guess you already have the code to loop through the pages.
I have check the dbo.tblChangeLog Table. There are million rows. Can i delete these all rows from this table ?
If you backed up your database then I believe there's nothing wrong in functionalities if you truncate that table (and if you don't use it at all, of course). But I also don't think truncating that table would help you in term of shrinking database size, because it'll grow sooner or later.
Please suggest me How can i do empty recycle bin. I tried to do empty recycle bin from Episerver Edit Mode but it does not empty. then i used (EXEC editDeleteChilds @PageID = 2, @ForceDelete = 1) query to delete the childs Pages of Recyclebin. but still its existed under recyclebin folder. Can you tell me why it is happening ?
I tried to shrink the log file. but now the database bigger than the earlier size. can you tell me why ?
Had a similar problem some years ago with a full recycle bin and no cap on maxversions. What we had to do was to do some SQL scripting to make it work. You can list all pages that belong to the trashbin, using tblTree and then you can delete them, using a cursor calling the stored procedure editDeletePage. As for cleaning out old versions, you can do a easily check top n worst pages by doing SELECT pkId, COUNT(*) FROM tblWorkPage GROUP BY pkID ORDER BY 2 DESC. Then you can call the stored procedure editDeletePageVersion for every work page id you want to delete.
The change log has a scheduled job in Admin mode to truncate it. Make sure this is enabled.