Database Optimization from 5.0 to 5.1
During the optimization proceess on the admin site, it returned a failed result with the following error stack:
System.InvalidOperationException: This SqlTransaction has completed; it is no
longer usable. at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.rollback() at
OpenAccessRuntime.Relational.conn.PsPoolConnection.rollback() at
OpenAccessRuntime.Relational.conn.PooledConnection.rollback() at
OpenAccessRuntime.Relational.RelationalStorageManager.rollbackImp(Boolean reset,
Boolean fromFinalizer) at
OpenAccessRuntime.Relational.RelationalStorageManager.rollback() at
OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.rollback()
I just got the actual SQL syntax and run it on the actual SQL server for the Sitefinity database. Runs faster and no errors, plus teh result is the same.
Where did you get the SQL from?
I'm getting the following error message on the optimization:
1.System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.2.at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.rollback() 3.at OpenAccessRuntime.Relational.conn.PsPoolConnection.rollback() 4.at OpenAccessRuntime.Relational.RelationalStorageManager.rollbackImp(Boolean reset, Boolean fromFinalizer) 5.at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.rollback() 6.A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)Hi,
First its imporatant to say: probably you have encountered some server timeout while performing this operation but it is absolutely safe to run it again. In order to do this you should open SystemConfig.config file located in the App_Data\Sitefinity\Configuration folder. There you should set (create) requireMaintenance attribute of the systemConfig tag to true.(see the example below). Then you should restart the application to apply this change and the performance optimization screen will appear when you access Sitefinity's backend. (This flag will be cleared at the end of the performance optimization and can be (re)set manually again if you need this).
<systemConfig requireMaintenance="true" xmlns:config="urn:telerik:sitefinity:configuration" …
Keep in mind that this operation can be performed regularly if you have lots of modifications to your data (e.g. after creating/deleting lots of stuff). It will improve your database index structure and your database will perform much faster.
The optimization can be ran manually by executing the SQL queries it executes.
Run this Query against the database of the project.
DELETE FROM dbo.sf_object_data_sf_permissionsWHERE id IN (SELECT id FROM [sf_object_data] WHERE page_id IS NULL AND parent_prop_id IS NULL AND id3 IS NOT NULL AND content_id IS NOT NULL) DELETE FROM [sf_object_data]WHERE page_id IS NULL AND parent_prop_id IS NULL AND id3 IS NOT NULL AND content_id IS NOT NULL DELETE FROM sf_permissionsWHERE id IN (SELECT sp.id FROM sf_permissions sp LEFT JOIN sf_object_data od ON sp.object_id = od.id WHERE ( sp.set_name = 'Controls' OR sp.set_name = 'LayoutElement' ) AND od.id IS NULL) DELETE FROM sf_control_propertiesWHERE id IN (SELECT sp.id FROM sf_control_properties sp LEFT JOIN sf_object_data od ON sp.control_id = od.id WHERE od.id IS NULL AND sp.control_id IS NOT NULL) DELETE FROM sf_control_propertiesWHERE id IN (SELECT sp.id FROM sf_control_properties sp LEFT JOIN sf_control_properties sp2 ON sp.prnt_prop_id = sp2.id WHERE sp2.id IS NULL AND sp.prnt_prop_id IS NOT NULL AND sp.control_id IS NULL)