SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Application stopped during Rebuild Index?


Application stopped during Rebuild Index?

Author
Message
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2492 Visits: 3465
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5196 Visits: 4076
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
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2492 Visits: 3465
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
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5196 Visits: 4076
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
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2492 Visits: 3465
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
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5196 Visits: 4076
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39341 Visits: 32624
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3963 Visits: 2235
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
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
one more thing are you doing it daily? and what timings? try online reindexing

Regards
Durai Nagarajan
SQL Galaxy
SQL Galaxy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2492 Visits: 3465
1. Reorganize index
2. Rebuild index

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search