Application stopped during Rebuild Index?

  • Hi,

    Version - SQL Server 2008 R2 64 bit.

    I created Maintenance plan for Re-organize and Rebuild index in single jobs.. Jobs successfully completed..

    During running job at that time application not able to connect the database.

    what could be issues? can check the option Index keeps available online in maintenance plan wizard?

    Thanks

    ananda

  • Are you saying that every database having connection issue or any specific ?

    Check the statuses of all databases

    select state_desc, * from sys.databases

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • state_desc is ONLINE..

    not for all database.. only user database

    Rebuild and Reorganize job took 40 min that time user not able to insert data thru application.. once completed job application become normal mode.

  • ananda.murugesan (9/24/2013)


    During running job at that time application not able to connect the database.

    What was the issue here ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • issuse is user not able to DML operation via application during Rebuilding and Reorganize index.

    I have doubt, i am not checked Index keep online in maintenance plan wizard? that is also possible? does block user transaction?

    Generally Rebuild or reorganize operation are resource intensive. during that jobs..I got call from user side for they are not able to do DML operation

  • ananda.murugesan (9/24/2013)


    I got call from user side for they are not able to do DML operation

    Often These maintenance tasks run at Off-Peak hours.(during nights). so avoid these kinds of conflicts.

    Database maintenance such as index reorg or rebuilds will pull a lot into cache, and updating stats will pull about 1% of the data into cache with default settings. These are expected. It’s also why these jobs should be kicked off outside of peak hours for that server.

    AND

    also you can do thses on some selective tables (scripts can be use to choose them).

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • In order to rearrange the index, SQL Server must put locks on that index. While locked, other users can't get access to it. The way around this is, if you're on Enterprise, to use ONLINE. That uses some of tempdb to store data in order to allow for access to the index while it is rebuilt. Other than that, only rebuild indexes during low load times as was already suggested.

    The trick is to understand why everything is locked during the forty minutes it takes to run. It should only be locking on any one index for a short period of time. It shouldn't just lock out a user. You should use sys.dm_exec_requests to understand why that user is blocked and what exactly they are waiting for.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Check if you can rebuild index online.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • one more thing are you doing it daily? and what timings? try online reindexing

    Regards
    Durai Nagarajan

  • 1. Reorganize index

    2. Rebuild index

    Can we create both operation in one single jobs via Mintenance plan?

  • ananda.murugesan (9/25/2013)


    Can we create both operation in one single jobs via Mintenance plan?

    Yes. on the left pane : maintenance plan task .. you will find both reorganize and rebuild tasks

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • You can combine any manner of things into one maintenance plan, that likely isn't your problem.

    Are you running Enterprise Edition?

    Jason Carter
    Tampa, Florida

    "Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young" - Henry Ford

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply