Recovery model RRS feed

  • Question

  • Hi,

    Please go through the below:

    In this link i observed that both in Bulk-logged and Simple are same. In this case which is the recommended one while rebuilding index. If point-in-time restore is doesn't matter.


    Fully logged

    Minimally logged

    Minimally logged

    • Edited by VijayKSQL Tuesday, January 8, 2013 4:27 PM mod
    • Moved by Papy Normand Thursday, January 10, 2013 12:23 AM not related to Data Access (From:SQL Server Data Access)
    Tuesday, January 8, 2013 4:26 PM


  • As Frank said, recovery model is not dependent on index rebuilds.  It is dependent on business need.

    If you do not need point in time recovery, there is no reason to not set the database to "simple" recovery.

    • Marked as answer by VijayKSQL Thursday, January 10, 2013 1:54 PM
    Thursday, January 10, 2013 1:53 PM

All replies

  • Hi,

    Depending on your business requirement the need for a recovery model would be dependent on point in time recovery. I would not think that it is dependent on index rebuild.

    Here is a link that will help explain the recovery models. The point of Bulk Logged and Simple are not the same.

    These links should give you an idea of how to deal with your scenario until an MVP responds.

    Hope this helps.


    Frank Garcia

    Tuesday, January 8, 2013 8:58 PM
  • Hello,

    Your question is not related to SQL Server Data Access according this link :

    "Questions and discussion about data access to SQL Server using SQL Native Client, OLEDB, ODBC, ADO, WDAC/MDAC, JDBC, or SOAP/HTTP"

    I prefer to move your thread in the Database Engine Forum

    I hope you will find a full and quick answer in this new forum

    Have a nice day

    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Tuesday, January 8, 2013 9:42 PM
  • Hi Vijay

    Please refer below link and details for better understanding

    Index Maintenance Question By paul S randal

    Q: I’ve changed our index maintenance routines to use online index rebuilds, but I’m still seeing blocking problems sometimes when the maintenance routines run. Why is this? I thought online index operations don’t use locks, so I shouldn’t see any blocking. Is this expected behavior or am I doing something wrong?

    A: You’re seeing expected behavior. There’s a shared table lock required at the start of the operation, while the operation initializes (a very fast process). This is immediately dropped. This lock must be queued like any other lock, and it will prevent any new queries from making modifications to the table until you can grant and release the lock again. You can’t acquire this lock until you’ve completed all currently running modification queries. This might take quite a while, depending on your workload. This means blocking may occur at the start of an online index operation. At the end of the operation, you have to take a schema-modification lock—think of this as an exclusive lock—to allow it to complete. This also happens extremely fast. Then you drop it immediately. This lock will prevent any type of new queries on the table (read or write) until you grant and release the lock. Once again, you can’t acquire this lock until SQL has completed all currently running read or write queries. This again means there’s the possibility of blocking. To summarize, although the name of the feature is online index operations, it does still require two short-term locks that can cause blocking problems. The gain over traditional offline index operations is that for the vast majority of the index operation, there are no locks held, and so overall concurrency is increased. The white paper “Online Indexing Operations in SQL Server 2005” has a lot more details on how these operations work. Reducing Index Maintenance Time

    Q: I’ve inherited some systems where regular index-maintenance jobs take a long time to run and generate lots of IO, but I don’t perform any index rebuilds because the indexes aren’t getting fragmented. I’d like to cut down on the work being done, as I’m not getting any performance gain. Can you recommend a strategy to help?

    A: This is a fairly common problem. It stems from the way index-maintenance jobs determine which indexes to rebuild or reorganize. Most people run the sys.dm_db_index_physical_stats dynamic management function (mentioned earlier) against all indexes in the database, then choose whether to rebuild, reorganize or do nothing. They base this decision on the avg_fragmentation_in_percent, the page_count and the avg_page_space_used_in_percent values using a WHERE clause on the output. The problem is that index fragmentation is not stored in memory like other statistics. This function must read and process each index to determine the extent of its fragmentation. If most of the indexes in the database are static or change very slowly (in terms of fragmentation), then they won’t be rebuilt or reorganized. Checking their fragmentation every time you run an index-maintenance job is essentially a waste of time. Most dynamic management views support “predicate push-down,” where the only data processed is that which matches the predicate in the WHERE clause. However, sys.dm_db_index_physical_stats is a function, not a view, so it can’t do this. This means you have to manually filter and only ask the function to process those indexes you know have the potential to be fragmented and may require rebuilding or reorganizing. I recommend monitoring fragmentation over the course of a few weeks. That way you get an idea of which indexes are worth checking for fragmentation, rather than checking everything. Once you have this list of indexes, create a table with the table name, index name and fragmentation threshold for taking action. You may find that some indexes can have more fragmentation before affecting performance than others. This will be the “driver table” you then use to drive the index-maintenance job. It should loop over all indexes described by the table and only run the sys.dm_db_index_physical_stats function on them. I’ve implemented this for several clients. In some cases, it has reduced the runtime of the index-maintenance job from hours down to 15 minutes or less. That’s purely from not running this function on static indexes. You could also go a step further and keep track of how often an index is rebuilt and potentially change the index’s FILLFACTOR setting automatically, hopefully leading to a further reduction in the work performed by the index-maintenance job.

    Thursday, January 10, 2013 10:02 AM
  • As Frank said, recovery model is not dependent on index rebuilds.  It is dependent on business need.

    If you do not need point in time recovery, there is no reason to not set the database to "simple" recovery.

    • Marked as answer by VijayKSQL Thursday, January 10, 2013 1:54 PM
    Thursday, January 10, 2013 1:53 PM