Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql2008-- index operation

Posted on 2014-01-26
2
Medium Priority
?
285 Views
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
  'ALTER INDEX ALL ON ? REBUILD WITH (SORT_IN_TEMPDB = ON)'
 -- force a checkpoint to reduce transaction log space used by the rebuild
 checkpoint
0
Comment
Question by:motioneye
2 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 1000 total points
ID: 39811752
hi

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
sql...
sql...
sql...
Commit/Rollback transaction.

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

Aaron.
0
 
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

581 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