Adding non-clustered indexs to a 100 million record table SQL2005

Posted on 2011-04-26
Last Modified: 2012-05-11
I am about to attempt to add a compisite index to a 30 gig    100million record table while online.

You guys see any pitfalls in doing this?  I am certain this will not affect my transication log at all. Well 99% sure.    
Question by:soccerman767
    LVL 17

    Accepted Solution

    As I mentioned in your other question, this is a very heavy duty operation and I wouldn't attempt it online.  Also, if you are in FULL recovery mode it WILL affect your transaction log.
    LVL 9

    Assisted Solution

    No Issues, go ahead. But your create index will put a exclusive lock during its execution and I may leads to unavailability of the table at that time. So Its better to go offline for some time and perform it.

    Also find some guidelines

    Author Comment

    This database is set to simple recovery mode
    LVL 21

    Expert Comment

    by:Alpesh Patel
    not an issue

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now