September 27, 2011 at 1:31 pm
Script: ALTER INDEX PK_compile_tblCredentials ON dbo.compile_tblCredentials REBUILD WITH (FILLFACTOR = 100, PAD_INDEX = ON,ONLINE = ON);
Error: Could not proceed with index DDL operation on table 'compile_tblCredentials' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE.
Scenario: Mirrored SQL 2k8. Autoshrink is disabled
This error comes immediately after running. I have seen very little as to the cause. In our testing sandbox (idential db's) I can rebuild it without issue. In Production (where the issue lies), I cannot even drop the index. It throws the same error.
Any help will be appreciated.
September 28, 2011 at 12:44 am
Hi,
As per my knowledge Rebuild is Offline operation, use reconfigure instead of rebuild.
Ali
MCTS SQL Server2k8
September 28, 2011 at 7:36 am
An ONLINE = ON option is available for the rebuilding of clustered indexes. Via TechNet:
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
I believe this has less to do with the REBUILD / REORG scenarios and more to do with some other underlying issue. The error implies more.
September 28, 2011 at 7:49 am
I assume you are running this against the principal database not the mirror? I have to ask.
Have you checked to see if the index rebuild is running already using the sys.dm_exec_sessions and sys.dm_exec_requests?
Have you run a DBCC CHECKTABLE against this table to make sure there is no corruption causing it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 8:25 am
No errors doing the DBCC CHECKTABLE. Not seeing anything out of the norm with sys.dm_exec_requests or sys.dm_exec_sessions and yes I'm attempting to run the REBUILD against the Principal not the Mirror
September 28, 2011 at 8:50 am
Can you check to see if the database is set to AUTOSHRINK?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 9:00 am
Triple checked and DISABLED / FALSE
September 28, 2011 at 9:51 am
If there are no other online index operations happening then I'm out of ideas.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 10:10 am
please run this query and find all the connections to db...
from the spid you should be able to find the operation by doing sp_who2 <spid>
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = '<db_name>'
go
from spid use this procedure to find the sql text
DECLARE @sql_handle-2 varbinary(64)
DECLARE @stmt_start int
DECLARE @stmt_end int
DECLARE @spid_i INT = 2011 /*Your Spid to check*/
SELECT @sql_handle-2 = sql_handle,
@stmt_start = stmt_start,
@stmt_end = stmt_end
from sys.sysprocesses with (nolock)
where spid = @spid_i
select text as 'Full Query',
case when @stmt_start > 0 then
case when @stmt_end > @stmt_start then
substring(text, @stmt_start/2, (@stmt_end/2)-(@stmt_start/2))
else
substring(text, @stmt_start/2, len(text))
end
else
null
end as 'Current Command', @stmt_start, @stmt_end
from sys.dm_exec_sql_text(@sql_handle)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply