sql2008-- index operation

Posted on 2014-01-26
Medium Priority
Last Modified: 2014-02-12
I read one of teh document for index operation in sql server, I don't understands below point by Mcsft, does it means here when I run index operation with begin or end transaction, thsi is call explicit transaction ?

4.      Do not run the online index operation in an explicit transaction. The log will not be truncated until the explicit transaction ends.

How about if I run it  below?

exec sp_MSforeachtable
 -- force a checkpoint to reduce transaction log space used by the rebuild
Question by:motioneye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 15

Accepted Solution

Aaron Shilo earned 1000 total points
ID: 39811752

index operations or any OTHER type of operation can be done within an EXPLICIT TRANSACTION.

an explicit transaction is when you do any sql operation within :

Begin transaction
Commit/Rollback transaction.

readup on managing transaction within sql server : http://technet.microsoft.com/en-us/library/ms188929.aspx

LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 1000 total points
ID: 39812494
You can execute the given statement within or without explicit transaction.
But putting it in a explicit transaction will reduce the performance of the index rebuild operation.

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question