Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 6:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1378208
Posted Tuesday, October 30, 2012 8:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 5:25 AM
Points: 1, Visits: 35
This is a very good post, thx
Post #1379136
Posted Tuesday, November 27, 2012 6:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:10 AM
Points: 2,622, Visits: 1,548
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
Post #1389481
Posted Wednesday, November 28, 2012 12:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1389544
Posted Wednesday, November 28, 2012 1:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:10 AM
Points: 2,622, Visits: 1,548

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
Post #1389573
Posted Wednesday, November 28, 2012 2:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1389585
Posted Wednesday, November 28, 2012 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:10 AM
Points: 2,622, Visits: 1,548
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
Post #1389602
Posted Wednesday, April 3, 2013 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 1, Visits: 90
Very Good Series Please share Next Levels.
Post #1438405
Posted Friday, April 12, 2013 8:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 11:52 AM
Points: 569, Visits: 1,024
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.
Post #1441756
Posted Thursday, November 7, 2013 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 7:19 AM
Points: 1, Visits: 54
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
Post #1512214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse