|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Regarding Transaction Log
SQL Server book online says “Log records are written to disk when the
transactions are committed.†In case of re-indexing how does it work. I understand the re-index is done as one single transaction which means the details of the transaction log should be written to disk only after the re-index is completed but I see the database transaction log grews during the re-index process itself. Can some body explain Hi
Reindexing is fully logged operation. I will try you explain as I undertsand how rebuild index does work very short. 1) SQL Server creates a new index (it will require additinal space) , as such it needs to lock the index , create a metadata... 2) After creating the index SQL Server inserting the data into the new index .. If the index operation creates or drops a clustered index and there are nonclustered indexes on the table, (for 1 and 2) are repeated twice; once for the new clustered index (or, for a drop operation, the heap), and again for the nonclustered indexes which are all rebuilt. 3) SQL Server drops old statistics and old indexes Show quoteHide quote "Balaji" <Bal***@discussions.microsoft.com> wrote in message news:71264C49-5CA5-486F-97CD-1FF2F12FA01D@microsoft.com... > SQL Server book online says “Log records are written to disk when the > transactions are committed.” In case of re-indexing how does it work. I > understand the re-index is done as one single transaction which means the > details of the transaction log should be written to disk only after the > re-index is completed but I see the database transaction log grews during > the > re-index process itself. Can some body explain Dear Uri,
I want to know when does the re-index operation flushes the log to disk from "Log Cache" during the re-index process. Show quoteHide quote "Uri Dimant" wrote: > Hi > Reindexing is fully logged operation. I will try you explain as I undertsand > how rebuild index does work very short. > > 1) SQL Server creates a new index (it will require additinal space) , as > such it needs to lock the index , create a metadata... > > 2) After creating the index SQL Server inserting the data into the new index > .. > > If the index operation creates or drops a clustered index and there are > nonclustered indexes on the table, (for 1 and 2) are repeated twice; once > for the new clustered index (or, for a drop operation, the heap), and again > for the nonclustered indexes which are all rebuilt. > > 3) SQL Server drops old statistics and old indexes > > > > > > > "Balaji" <Bal***@discussions.microsoft.com> wrote in message > news:71264C49-5CA5-486F-97CD-1FF2F12FA01D@microsoft.com... > > SQL Server book online says “Log records are written to disk when the > > transactions are committed.†In case of re-indexing how does it work. I > > understand the re-index is done as one single transaction which means the > > details of the transaction log should be written to disk only after the > > re-index is completed but I see the database transaction log grews during > > the > > re-index process itself. Can some body explain > > > Hi
As I mentioned each operation is fully logged , it creates a new indexes so its logged, it inserts a new data, again its logged. Therefore , it is not good idea to run re-index on VLDB as it may affect perfomance an tables will be locked. Instead, the DBA should identify heavily fragmented tables and rebuild indexes on them . There is also DBCC INDEXDEFRAG commad , have you read about it? Show quoteHide quote "Balaji" <Bal***@discussions.microsoft.com> wrote in message news:CCE110B9-77CD-4D53-8440-9628FF366835@microsoft.com... > Dear Uri, > > I want to know when does the re-index operation flushes the log to disk > from > "Log Cache" during the re-index process. > > "Uri Dimant" wrote: > >> Hi >> Reindexing is fully logged operation. I will try you explain as I >> undertsand >> how rebuild index does work very short. >> >> 1) SQL Server creates a new index (it will require additinal space) , as >> such it needs to lock the index , create a metadata... >> >> 2) After creating the index SQL Server inserting the data into the new >> index >> .. >> >> If the index operation creates or drops a clustered index and there are >> nonclustered indexes on the table, (for 1 and 2) are repeated twice; once >> for the new clustered index (or, for a drop operation, the heap), and >> again >> for the nonclustered indexes which are all rebuilt. >> >> 3) SQL Server drops old statistics and old indexes >> >> >> >> >> >> >> "Balaji" <Bal***@discussions.microsoft.com> wrote in message >> news:71264C49-5CA5-486F-97CD-1FF2F12FA01D@microsoft.com... >> > SQL Server book online says “Log records are written to disk when the >> > transactions are committed.” In case of re-indexing how does it work. I >> > understand the re-index is done as one single transaction which means >> > the >> > details of the transaction log should be written to disk only after the >> > re-index is completed but I see the database transaction log grews >> > during >> > the >> > re-index process itself. Can some body explain >> >> >> BOL oversimplifies things. It should say something like:
Log records are force-written at commit. They can be written at an earlier time (for instance when a checkpoint occurs and a dirty page is written which contains modifications which hasn't been committed yet). -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Balaji" <Bal***@discussions.microsoft.com> wrote in message news:71264C49-5CA5-486F-97CD-1FF2F12FA01D@microsoft.com... > SQL Server book online says "Log records are written to disk when the > transactions are committed." In case of re-indexing how does it work. I > understand the re-index is done as one single transaction which means the > details of the transaction log should be written to disk only after the > re-index is completed but I see the database transaction log grews during the > re-index process itself. Can some body explain  
Other interesting topics
Optimizing and shrinking large highly-transactional database
Update Timeout and indexes Trigger to fire only after a commit. copy many ssis packages between servers Is the same? Linked Server Properties - Security - SQL Server 2000 Logging from SP CPU dedicated to one Stored Procedure Backing up to a server on another domain ? Stranger than Fiction: differences in Edit vs Design a view |
|||||||||||||||||||||||
