Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Application stopped during Rebuild Index? Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 4:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 1,045, Visits: 2,933
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
Post #1497747
Posted Tuesday, September 24, 2013 4:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1497755
Posted Tuesday, September 24, 2013 4:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 1,045, Visits: 2,933

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.


Post #1497758
Posted Tuesday, September 24, 2013 4:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1497760
Posted Tuesday, September 24, 2013 4:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 1,045, Visits: 2,933
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
Post #1497765
Posted Tuesday, September 24, 2013 6:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1497793
Posted Tuesday, September 24, 2013 7:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,518, Visits: 27,899
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1497833
Posted Tuesday, September 24, 2013 8:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1497888
Posted Tuesday, September 24, 2013 10:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
one more thing are you doing it daily? and what timings? try online reindexing



Regards
Durai Nagarajan
Post #1498115
Posted Wednesday, September 25, 2013 3:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 1,045, Visits: 2,933

1. Reorganize index
2. Rebuild index

Can we create both operation in one single jobs via Mintenance plan?
Post #1498212
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse