+1-888-365-2779
Try Now
More in this section

Forums / General Discussions / Database Optimization from 5.0 to 5.1

Database Optimization from 5.0 to 5.1

5 posts, 0 answered
  1. Vincent
    Vincent avatar
    4 posts
    Registered:
    11 Sep 2012
    18 Sep 2012
    Link to this post
    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()
  2. Andrei
    Andrei avatar
    10 posts
    Registered:
    16 Apr 2012
    18 Sep 2012
    Link to this post
    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.
  3. Compton
    Compton avatar
    18 posts
    Registered:
    02 Feb 2012
    15 Oct 2012 in reply to Andrei
    Link to this post
    Where did you get the SQL from?
  4. Webdev
    Webdev avatar
    6 posts
    Registered:
    20 Sep 2012
    26 Dec 2012
    Link to this post
    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)
  5. Stanislav Velikov
    Stanislav Velikov avatar
    1113 posts
    Registered:
    30 Nov 2016
    01 Jan 2013
    Link to this post
    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_permissions
    WHERE  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_permissions
    WHERE  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_properties
    WHERE  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_properties
    WHERE  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)

    The main optimization query that fixes the performance problems is shown here, execute the second sql query and the full optimization will pass.

    Run those two SQL queries against the database and the site should run (this was my experience encountering it and it was always running after).
    Basically the project will start after the upgrade without the optimization completing. The optimization can be ran on demand later or when needed.

    You have 2 options - to continue working and leave the optimization. Everything should work as expected. The second option is to try to run the optimization again (You could restart the performance optimization process from Administration > Settings > Advanced > System and check RequireMaintenance - then save changes and restart the web site and make a request to the site's backend.
    Please keep in mind that during the performance optimization task you should be patient and do not refresh the page because you might get errors. Let me know if some of the two options is working for you and you are able to run the project or run the optimization again.


    All the best,
    Stanislav Velikov
    the Telerik team
    Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
5 posts, 0 answered