April 9, 2012 at 11:09 am
I am reading an article http://www.mssqltips.com/sqlservertip/1299/sql-server-dba-interview-questions-part-1/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201248#comments
It says what is the difference between index reorganizaiton and index rebuild.
The answer is a reorganization is always a fully logged operation, but a reindex can be a minimally-Logged operation.
But when we have our reindex jobs at weekend maintenance plan, it seems it used space that double the size of the databases?
So it seems the answer is differenct from what we see about our index job.
Any ideas?
Thanks
April 9, 2012 at 11:13 am
sqlfriends (4/9/2012)
I am reading an article http://www.mssqltips.com/sqlservertip/1299/sql-server-dba-interview-questions-part-1/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201248#commentsIt says what is the difference between index reorganizaiton and index rebuild.
The answer is a reorganization is always a fully logged operation, but a reindex can be a minimally-Logged operation.
That's correct, rebuilds can be minimally logged. It's far from the only difference between the two, but it is one difference.
But when we have our reindex jobs at weekend maintenance plan, it seems it used space that double the size of the databases?
Space in the data file or space in the log file?
Note that the article says that the rebuild can be minimally logged. Not that it will always be minimally logged. See Books Online for recovery models and minimally logged operations.
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
April 9, 2012 at 11:27 am
But when we have our reindex jobs at weekend maintenance plan, it seems it used space that double the size of the databases?
Space in the data file or space in the log file?
Note that the article says that the rebuild can be minimally logged. Not that it will always be minimally logged. See Books Online for recovery models and minimally logged operations.
Sorry, now I remember it seems the space is doubled in data file, not log file, is that supposed to be correct? and also our transaction log back up file after reindex has a large size too.
Does that mean if the recovery mode is full which is our case, the reindex will be a fully -logged instead of minimally logged?
thanks
April 9, 2012 at 11:31 am
sqlfriends (4/9/2012)
Sorry, now I remember it seems the space is doubled in data file, not log file, is that supposed to be correct?
Yes, SQL needs somewhere to put the new index.
Does that mean if the recovery mode is full which is our case, the reindex will be a fully -logged instead of minimally logged?
What does Books Online say about the recovery model requirements for minimal logging?
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply