Application stopped during Rebuild Index?

Question

Post reply

Application stopped during Rebuild Index?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 24, 2013 at 4:05 am

#299132

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

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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;-)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

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."

durai nagarajan SSCoach Points: 15778 More actions · Answer 8

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

Regards
Durai Nagarajan

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

1. Reorganize index

2. Rebuild index

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

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;-)

Jason Carter SSC Enthusiast Points: 153 More actions · Answer 11

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