Post reply

Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth

  • October 29, 2012 at 6:07 am

    #269124

    Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 30, 2012 at 8:30 pm

    #1554466

    This is a very good post, thx

  • November 27, 2012 at 6:44 pm

    #1562905

    Good stuff to read. A question on index rebuild

    Index rebuilds

    Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

    I read an article by Paul on index rebuild. In his blog, he mention "Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy". This is what I thought, otherwise rolling back an offline index rebuild will take a considerable time.

    http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-(allocation-BULK_LOGGED-mode-locking).aspx

    It seem this article states offline index rebuild will drop the existing index before creating a new index?

    Just wanted to clarify if offline index really do drop existing index before the new ones are created and under what circumstance will this happen. i.e. does the statement mean logical metadata are marked "deleted" but actual index still exists, etc.

    Thanks in advance for explaination.

    Simon

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • November 28, 2012 at 12:36 am

    #1562949

    Simon-413722 (11/27/2012)

    It seem this article states offline index rebuild will drop the existing index before creating a new index?

    Not sure where you saw that. Paul stated in the first section:

    Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy.

    The old index is read to create the new one and only dropped when the rebuild is complete. That's why you need at least as much free space as the size of the index in the data file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 28, 2012 at 1:49 am

    #1562976

    Index rebuilds

    Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

    Hi Gail,

    I copied and paste above paragraph from this article. From reading it, i got the wrong impression that offline reindex rebuild drops target index and then rebuild, whilst online index does not drop existing index until the of index rebuild.

    You're right,alter index rebuild reads from existing index pages even if they're corrupted pages.

    Thanks for clarification.

    Simon

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • November 28, 2012 at 2:23 am

    #1562984

    Simon-413722 (11/28/2012)

    Index rebuilds

    Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

    Hi Gail,

    I copied and paste above paragraph from the article.

    Urgh... looks like I missed that in tech-editing (Tony wrote this article, I checked it for errors). I will set an appropriate penance and fix it later.

    Sorry, I misunderstood where you read what from. Paul is, of course, correct.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 28, 2012 at 2:56 am

    #1562999

    No worries Gail.

    I dont post much here and unaware how to put a paragraph into quote. I know now and edited my first comment and put it the quote from article. It would've been much easier for you to read my comment.

    Simon

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • April 3, 2013 at 8:55 am

    #1603222

    Very Good Series Please share Next Levels.

  • April 12, 2013 at 8:56 am

    #1606012

    Sorry for the very slow response - for some reason I'm not receiving comment notifications on this article, and I missed these comments.

    Firstly, I've now corrected the text with regard to offline/online rebuilds. Thanks, Simon!

    Secondly, Level 8 will be published on April 17 and Level 9 on April 24.

    Best,

    Tony.

  • November 7, 2013 at 5:34 am

    #1664719

    Hello,

    Concerning the release of vlf's in a transactional replication operating in "pull", I noticed that as the distribution of jobs have not finished their work vlf's are not released.

    Regards

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply