locked
unsupported read operations for MOSS 2007 SP1 databases RRS feed

  • Question

  • Hi,

    We have SQL Server 2005 EE x64 with SP3 and have MOSS 2007 SP1 databases in it. I went through the belwo link to know the MS Support for changes to the Share point databases

    http://support.microsoft.com/kb/841057/

    According to the above link:

    Performing any read operations directly against these databases is unsupported. Reading from these databases programmatically or manually could cause unexpected locking within Microsoft SQL Server that can result in overall performance problems.

    In the event that unsupported read operations are discovered as part of a support call, the database will be considered to be in an unsupported state. To return the database to a supported state, the customer must cease all unsupported read activities, regardless of whether they are being performed manually or programmatically, and may need to a database restoration from the last known good backup that did not include unsupported read activities



    But I used the below script to find out the Index fragmentation for Share Point databases:

    SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, 'limited')
    where avg_fragmentation_in_percent>10 AND page_count>1000
    order by page_count desc

    Question:

    Does executing the above query (read operation) comes under unsupported read operations according MS?

    If that is unsupported read operation, how can we know the Index fragmentation and how can we know when to perform Index defrag?

    please advice
    Thursday, February 18, 2010 6:52 PM

Answers

  • There are a number of Third Party Vendors that monitor SQL and SharePoint Fbs on SQL (Read Only), You need to read the next Article that is linked on the page

    932744  (http://support.microsoft.com/kb/932744/ ) Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases http://support.microsoft.com/kb/932744/

    The Maintenance Plan Wizard in SQL Server 2005 enables administrators to perform the following maintenance tasks against SharePoint databases:

     

    • Check database integrity
    • Reduce a database
    • Reorganize an index
    • Clean up the history
    • Update statistics
    • Rebuild an index 

    We have tested these tasks and the effects that these tasks have on database schema and performance. The following table summarizes the results of these tests.

    Collapse this tableExpand this table

    Task

    Safe to perform this task?

    Check database

    Yes

    Reduce a database

    Yes

    Reorganize an index

    Yes

    Clean up the history

    Yes

    Update statistics

    Yes. However, this task is unnecessary because the SharePoint Timer service performs this task automatically.

    Rebuild an index

    No. The task does not restore existing index options before the rebuild operation. However, you can use scripts that restore index options.

    Note This problem was corrected in SQL Server 2005 Service Pack 2.

     

    We used the following criteria to determine whether a task was safe to perform:

     

    • Whether the task modified the database schema from its default state
    • Whether the task decreased performance 

    Results may vary depending on the environment. However, if you use the Maintenance Plan Wizard to perform the tasks that are listed in the table as "safe to perform," you are likely to experience increased performance in SQL Server 2005.

    As long as the Monitoring Tools being used to not in anyway modify the SharePoint Dbs in any way and do not impact performance, especially if you open a SRX ticket describing decreased performance... Common sense is to continue using the Monitoriung tools to escalate operational issues within your organizations in a timely manner... 

    However, if one of the Moderators reading this  post would escalate this issue through Microsoft it would seem appropriate... This would give everyoine piece if mind, no one wants to be in an unsupported state.



    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Friday, February 19, 2010 6:14 AM

All replies

  • There are a number of Third Party Vendors that monitor SQL and SharePoint Fbs on SQL (Read Only), You need to read the next Article that is linked on the page

    932744  (http://support.microsoft.com/kb/932744/ ) Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases http://support.microsoft.com/kb/932744/

    The Maintenance Plan Wizard in SQL Server 2005 enables administrators to perform the following maintenance tasks against SharePoint databases:

     

    • Check database integrity
    • Reduce a database
    • Reorganize an index
    • Clean up the history
    • Update statistics
    • Rebuild an index 

    We have tested these tasks and the effects that these tasks have on database schema and performance. The following table summarizes the results of these tests.

    Collapse this tableExpand this table

    Task

    Safe to perform this task?

    Check database

    Yes

    Reduce a database

    Yes

    Reorganize an index

    Yes

    Clean up the history

    Yes

    Update statistics

    Yes. However, this task is unnecessary because the SharePoint Timer service performs this task automatically.

    Rebuild an index

    No. The task does not restore existing index options before the rebuild operation. However, you can use scripts that restore index options.

    Note This problem was corrected in SQL Server 2005 Service Pack 2.

     

    We used the following criteria to determine whether a task was safe to perform:

     

    • Whether the task modified the database schema from its default state
    • Whether the task decreased performance 

    Results may vary depending on the environment. However, if you use the Maintenance Plan Wizard to perform the tasks that are listed in the table as "safe to perform," you are likely to experience increased performance in SQL Server 2005.

    As long as the Monitoring Tools being used to not in anyway modify the SharePoint Dbs in any way and do not impact performance, especially if you open a SRX ticket describing decreased performance... Common sense is to continue using the Monitoriung tools to escalate operational issues within your organizations in a timely manner... 

    However, if one of the Moderators reading this  post would escalate this issue through Microsoft it would seem appropriate... This would give everyoine piece if mind, no one wants to be in an unsupported state.



    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Friday, February 19, 2010 6:14 AM
  • > However, if one of the Moderators reading this  post would escalate this issue through Microsoft it would seem appropriate... 

    It would be nice to think that forum Moderators have escalation possibilities.

    Unfortunately forum Moderation doesn't necessarily lead to improved Microsoft contacts.


    Maybe the official Microsoft support people here will be able to push this up the line. I can't.


    Mike

    FAQ sites: (SP 2010) http://wssv4faq.mindsharp.com; (v3) http://wssv3faq.mindsharp.com and (WSS 2.0) http://wssv2faq.mindsharp.com
    Complete Book Lists (incl. foreign language) on each site.
    Friday, February 19, 2010 7:38 AM
  • Hi Mike,

    Does executing the below query on Share point databases comes under unsupported read operation as per MS?

    SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, 'limited')
    where avg_fragmentation_in_percent>10 AND page_count>1000
    order by page_count desc

    thanks


    Friday, February 19, 2010 6:19 PM
  • any more thoughts on this will be very helpful in order to know what maintenance queries to run and what not to run on Share point databases

    thanks
    Monday, February 22, 2010 7:45 PM