Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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


343772501
343772501
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 35
This is a very good post, thx
SimonLiew
SimonLiew
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 1769
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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


SimonLiew
SimonLiew
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 1769

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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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


SimonLiew
SimonLiew
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 1769
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
abhishekgupta1986
abhishekgupta1986
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 273
Very Good Series Please share Next Levels.
Tony Davis
Tony Davis
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: Administrators
Points: 591 Visits: 1148
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.
Lancyo
Lancyo
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 71
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search