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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Rebuilding indexes

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server



Is it good to do index rebulid with online  option when production is going on. Is there any impact?

Kindly let me know if there is any impact.

We are using Sql server 2005 standard edittion.

Thanks in advance....

View Complete Post

More Related Resource Links

On rebuilding indexes of MSmerge_Contents and other replication system tables



In my previous question leads me to the index rebuild of MSmerge_Contents system table. However, I am still not sure what the explicit and correct way to rebuild index is. 

Refering to examples in BOL, the syntax should be something like the following:

ALTER INDEX ALL ON msmerge_contents

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Do I need to specify ot

ArrayList indexes not set properly


I have some basic code to fill items in an ArrayList as follows:

Dim intSteps As ArrayList = New ArrayList()
Select Case Session("type")
Case 1
Case 2
Case Else
End Select

Dim j As Integer
	For j = 0 To intSteps.Count - 1
        Response.Write(intSteps.IndexOf(j) & " : " & intSteps.Item(j) & "<br />")

The issue is that for Case 1 the output is:

0 : 0
1 : 1
2 : 2
-1 : 6
-1 : 11
-1 : 12

For Case 2, it's:

0 : 0
1 : 1
2 : 2
-1 : 5
-1 : 6
3 : 11
4 : 12

And for Else, it's:

0 : 0
1 : 1
2 : 2
3 : 3
4 : 4
5 : 5
6 : 6
7 : 7
8 : 8
9 : 9
10 : 10
11 : 11
12 : 12

Why is the index being set as -1 for a few items?

How to remove indexes

How to remove indexes..as it is created by mistake I have read that if it will be deleted then data will be deleted also...PLease clear the case of removal of clustered and non clustered indexes..."SQLSERVER DBA" "INDIA"

Find all compressed indexes?

I've inherited a database on which a contractor was toying around with compressing indexes. As the work she did was never documented I've been asked to remove all compression from the database so that the front end vendor can test a service pack. Is there a simple way to either uncompress all of the compressed indexes within a database or to rebuild all existing indexes within the database without compression? Thanks for your time, XOXO CD.

How to tell if 2 or more indexes are interleaved?

Does anyone know if there is a quick and easy way to tell if indexes are interleaved or not?John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

not all indexes used in query?

hello, I have this query with several joined tables, and i have created an index on the columns used in the joins. in the query below I join tables hr200, hr300, hr301 and dim_datum. I created the following indexes: In hr200: index on hr200_obj in hr300: index on hr300_obj, hr200_obj in hr301: index on hr301_obj, hr300_obj in dim_datum: index on datum_id, datum But when i run below query, only the index on dim_datum is used. What to do to malke the query use the other indexes too?   SELECT   dbo.hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum, CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate() END   AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID, CAST   (CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY, CAST   (CONVERT(char(8), dbo.hr300.hr300_date_end, 112) AS int) AS ZiekEind_KEY, DATEDIFF   (dd, dbo.hr300.hr300_date_start, dbo.hr300.hr300_date_end) AS Verzuimduur, dbo .hr301.hr301_date, dbo .hr301.hr301_percentage_sick / 100 AS PercentageZiek, dbo .hr301.hr301_percentage_recovered

Is there a way to make SPS 2003 search fields search the MOSS indexes?

Here's my situation: We're doing a gradual migration from SPS to MOSS. Can anyone tell me if there's an easy way to make the SPS search fields search the MOSS indexes? The easiest way I can think of is to great a web part for SPS which will gather the scope and search param and redirect the user to the MOSS search result page. If there's an easier way. I'm all ears.

will indexes auto sort?

  is it true that if we create indexes for our tables, we must still explicitly declare "order by <the index>" when we return a select*from table or can i assume that it is ordered as defined by my index without me having to explicitly state order by?    

Multiple indexes using same column

I am writing an extensive query and have multiple non-clustered indexs on a table.  I have one index for a single column [Column1] and another index for [Column2], [Column1].  I am using different querys where I sometimes just use [Column1] and other times use many columns including both [Column1], and [Column2]. My question is: If you have multiple indexes that contain the same column will they ever cause a conflict?

rebuilding index

I am having a table with indexes and I run a SSIS package once in a month i.e is scheduled.So I want to drop the indexes before loading the data and rebuild the indexes after loading the data.Can any one suggest to drop and rebuild index.

Strategy In creating indexes

Hi Team, Need a help strategy in creating indexes.. I have a Query which returns me more then 4 billion rows, which i am trying to insert into a table. There are views and table in the query. I am working on SQL Server 2000. So i thought if we create indexes on primary key columns on the table(which are used in views), on the columns used where condition of the main query. Will creating clustered index on the table which more than one primary key helpful? Is my strategy correct? any suggestions will be of great help! Regards, Eshwar.

URGENT - Run out of HD space whilst rebuilding an index

I just altered a table on my large SQL server 2000 database and now the database has been offline for ages and the hard drive as run out of space because of the transaction log size.What can I do!  Enterprise manager has frozen me out completely and theres nothing else I can delete to free up space whist this completes.I've not received any kind of warning and the HD is still being accessed by SQLserver.Do you think this will eventually complete?  Should I stop the SQL service.  HELP

No Indexes - Linking to MS Access

Hello, I am using MS Access 2000 as a front end to a SQL Server 2000 db.  When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link.  The pk's and indexes do exist in SQL Server.  I haven't had to link to these tables in over a year, but they used to link just fine. Do you have a suggestion as to where to look at this problem? Thanks you - Darryle

Problem with cursor and Rebuild indexes

(SQL Server 2008 SP1) Hi I have a stored procedure that runs frequentely (uses a cursor), that SP never have errors on execution, but when runs at the same time with a rebuild index task (Database maintenance task on sundays), the stored procedure show the following error: "Could not complete cursor operation because the table schema changed after the cursor was declared" My question: Is there any way to avoid this problem running the two task at the same time? Is this a bug of the SQL Server 2008?JAC

Partitioning Indexes

I have a partitioned table that has a partitioned index.  If I add another index does it have to be partitioned as well or can I have some partitioned indexes and some not? Thanks.

Error While Rebuilding system databases

Hi, I installed my SQL Server with a wrong collation. I tried to rebuild the instance and when i tried doing that I am getting a weird error as given below. ALso the installation is picking the edition as evaluation instead of enterprise, i tried executing the install file and it went through to install a enterprise edition. I am not sure why the installer is picking up the evaluation edition instead of enterprise as the option is set.  Exception summary: The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indented Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException     Message:          The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details. SugeshKumar SQL Server MVP http://sugeshkr.blogspot.com

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)


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