.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

Where are the index rebuild transactions stored during an online index rebuild with SORT_IN_TEMPB se

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
I am reposting this from another forum. In this article http://msdn.microsoft.com/en-us/library/ms184246(v=SQL.100).aspx, it is stated that "The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. This allows for the transaction log of the user database to be truncated during the index operation if it is required." However, this doesn't seem to be the case for me. I am running Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) on a Windows Server 2008 x64 EE server in FULL recovery mode with all user databases set to 100 for their compatibility mode.  The server has 48GB of ram.  I have a nightly job that runs ALTER INDEX REBUILD or ALTER INDEX REORGANIZE for each index in a user database. The SP called by the job determines if the table has any underlying LOB datatypes and if so will not attempt an online rebuild, it also determines whether to rebuild or reorganize based off the fragmentation percentage of the index and does not use explicit transactions.  There are almost no concurrent user transactions running when I run the index rebuild job.  Each night, the user database log blows up during the index rebuild job (or it would if I didn't have a job in place that checked the amount of used space in the log and cancelled the inde


View Complete Post


More Related Resource Links

Maintenace Plan - Index online rebuild failed

  

Hi,

on my sql 2008, using Wizard, I created a Maintenance Plan with one of the tasks - to rebuild indexes online. It is important for us that indexes are rebuilt online.

I've got the following error:

Executing the query "ALTER INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'aspnet_Membership_index' because the index contains column 'Comment' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What are my options?

thank you!


Maintenance plan to rebuild Full-text index ? (SQL 2005)

  
Is it possible, through the GUI Wizard, to create a Maintenance plan that rebuilds (rather than reorganise/optimise) a Full text index in 2005 ? The full text index is based on one table only - so would using the "Rebuild index task" in the Wizard do the trick ? I wouldn't mind if the other indexes on the table were rebuilt too, but main concern in the Full text Index. If not - then I guess it would be a scheduled job which does a ALTER FULLTEXT CATALOG catalog_name REBUILD, but I thought it would be nice to keep it within the - Management - Maintenance plans - section of SQL Management Studio. Also - should this be preceeded with a transaction log backup ? Had some issues rebuilding them last night - seemed a Log backup was needed.

Rebuild index failed

  
I scheduled a maint plan to rebuild index. All table's indexes are successfully built except for one table. The error message I got:   Failed:(-1073548784) Executing the query "ALTER INDEX [Index_name] ON [dbo].[table] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY  = OFF, ONLINE = ON )" failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Can someone tell me what the problem is and how to fix it? Thanks.

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?

  

As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)

TIA


index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)

  

index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)

database grew from 40 GB to 500 GB. space is reserved no free space to reclaim after reindex completes.

pls guide


Index Rebuild in SQL 2005

  

I am doing a single pk index rebuild on a 100 gig table.  After running sp_who2, I see that the rebuild in suspended state.  Over a period of time, I have noticed that neither disk io nor cpu io is changing.   Is there a way for me to cancel it?  Is it frozen forever?

Any help is appreciated 


Rebuild Index Error -1073548784

  

I am getting the following error on Rebuild Index Maintenance Task.

"Executing the query "ALTER INDEX [PK_cartrige] ON [dbo..." failed with the following error: "A severe error occurred on the current command.  The results, if any, should be discarded.
A severe error occurred on the current command.  The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Where can i find more details on troubleshooting this error and find more details on the error. It seems to be a persistant problem since sql server 2005.

Details:

  1. -Maintenance Plan has been working well for months.
  2. -Std Edition Sql Server 2008
  3. -Db Integrity check successfull
  4. -sort result in tempdb is off
  5. -keep index online while reindexing is off
  6. -local server connection works for all other jobs prior to the task

Rebuild Index and Update Column Statistics

  

As Index rebuild process will create and update stats, we should not update stats as the row sampling would be less than ideal. However, here is my question. Would a column Stats need to be updated after a Index Rebuild.

If yes, why, if no why. Please provide some documentation to get a better understanding of this with the help of an example. 

Thank you.


Index Rebuild failing with sql error msg 8966, level 16, state 2, line 20

  

Working with a customer trying to help him re-index his database. Database is a little over a terabyte and I gave him a re-indexing script to run on his SQL Server 2005 Enterprise server.

FYI
It had been recommended by a 3rd party load testing company that all of our re-indexing be done in "offline" mode so the re-indexing was done in offline mode for this customer.

The first reindex attempt stopped with a "severe" error message in the "query window" when sql reached the largest table in the system, which I found out contains about 29 milliion rows. The previous indexes had been successfully rebuilt or reorganized based on the percentage of fragmentation.

I asked for the sql error logs and found the "severe" error message written to the log around the time of a "sql server restart". I asked the customer and he admitted to restarting sql server. I suspected that the reindex script had not completed but could not be sure.

I decided to approach it next by rebuilding the index on the huge table only. He called me the next day and told me the rebuild had failed. He sent me the error logs and he also informed me he had killed the index query after it had been running for over 24 hours.

I found some IO errors in the sql error logs referring to "15 seconds" had been exceeded a

Rebuild index

  
I Rebuild index on the table when Fragementation occurs then i have one doubt that is when we do the rebuild and reorganize on the table, what is the priority of locks on the table.

SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea

  

I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

how to create index for a column

  

Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.



Thanks..,


Deleting items from the index (custom protocol handler)

  
I created a custom protocol handler. I successfully emit entries into the index. I just can't seem to delete them during an incremental crawl.

I tried:

1. no emitting child item during the enumeration of child entries in a folder
2. returning PRTH_E_OBJ_NOT_FOUND from CreateAccessorEx

doing either 1, 2, or both did nothing. The entries are still there.

what i need to do to delete these entries from the index during an incremental crawl?

Thanks.

Ingest google Search Index into Sharepoint 2007

  

Hi Folks

 I am wondering  if there is a way to ingest google search index into SharePoint index such that when people search via SharePoint they see the Google results too… incorporated/federated inline with the SP results (not as a separate "box" ).

We have Google search in our environment and also share-point search. Share-point search for sharepoint site but Google search is for all other environments. Now i am thinking if we ingest the Google search index into share-point then may be its great deal to provide user a platform to search everything on one place.

 

thanks

ws


i am at entry level administrator

Can't start crawl becasue of index move operation

  

Hi there,

I can't start crawl task. The log says that "Deleted by the gatherer (The start address or content source that contained this item was deleted and hence this item was deleted.)" But I did not change the path of content sources and When I trie to start crawling job it says "Crawling might be paused because a backup or an index move operation is in progress. Are you sure you want to resume this crawl?"

What is index move operation? What should I do? I'll really appreciate the solution greatly. Thanks in advance.

 


allan

"Content for this URL is excluded by the server because a no-index attribute." in crawl logs

  

Hi All,

I am getting following error message in Crawl Logs

" Content for this URL is excluded by the server because a no-index attribute. "

Any help in this regard will be greatly appreciated.

 

 


Contact Selector - Form tab index is lost after selector resolves names on blur

  

I'm working with an InfoPath2007 form developed to be filled-out through the browser on a SharePoint 2007 site, and I'm testing using Internet Explorer 7.  It's a straight-forward form, with a number of text fields and two Contact Selector controls.

The issue I'm encountering is with the Contact Selector control, where if the actor only enters a partial name into the text-field and then TABs away from the control the form will perform its auto-postback to look-up and resolve the partial name (showing the name resolution dialog is necessary), and afterward the actor's cursor is focused on the next field in the form.  The actor can type text into the focused field, but any subsequent pressing of the TAB key returns the actor's focus to the browser's ADDRESS bar, which is incorrect as it should proceed to the next field in the form.  I understand that the Contact Selector needs to perform its postback to perform the name resolution (so that there is no way through the InfoPath form designer to disable postbacks for the control), but why does the browser not know how to continue the TAB order of the fields on the form even when a field has focus?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend