Sql-server – Index rebuild canceled because of .mdf growth

The reason that your index rebuild isn't completing is because of the LCK_M_SCH_M wait type. What happens when you try to rebuild an index, a Sch-M lock is requested on the object that you're trying to rebuild.

Please see this below chart for lock compatibility:

As you can see here, a Sch-M lock has a conflict with almost every locking scenario (shared, exclusive, update, schema stability, etc.).

Here's a small example showing what may be happening in your environment. To create the test object in a test database:

use TestDB;
go

create table dbo.ConcurrencyTest
(
    id int identity(1, 1) not null
        constraint PK_ConcurrencyTest_Id primary key clustered,
    some_int int not null
        default 1
);
go

insert into dbo.ConcurrencyTest
default values;
go 100

Now if one session is executing a query, and it keeps the lock open (I'm using an update query and not committing the transaction):

use TestDB;
go

begin tran;

    update dbo.ConcurrencyTest
    set some_int = 2
    where id = 7;

--commit tran;

And if another session attempts to rebuild the clustered index on that table:

use TestDB;
go

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest
rebuild;
go

It's going to be blocked by the initial UPDATE query. We can see this through a little diagnostic query below:

select
    l.resource_type,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    st.text as blocked_sql_text,
    r.blocking_session_id,
    stb.text as blocking_sql_text
from sys.dm_tran_locks l
inner join sys.dm_exec_connections c
on l.request_session_id = c.session_id
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
inner join sys.dm_exec_connections cb
on r.blocking_session_id = cb.session_id
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
cross apply sys.dm_exec_sql_text(cb.most_recent_sql_handle) stb
where l.resource_database_id = db_id('TestDB')
and l.request_status = 'WAIT'
and r.blocking_session_id is not null
and r.blocking_session_id > 0;

My output looks like the following:

As you can see here, my ALTER INDEX ... REBUILD command is being blocked by the UPDATE query. The request lock is the Sch-M lock that is required on the OBJECT in order for this operation to complete.

Due to this concurrency conflict, it is advisable to schedule your index maintenance (as well as other maintenance tasks) during a window where there is little to no user load.

Points:

1. RAID 5 is a poorly performing configuration for database files especially for writes, and

2. Partitioning is even worse.

Yes, it works and is reliable, but it's definitely a low-performance disk configuration that I would only use myself for development or the most light-weight of apps. The standard configuration for the physical host of a SQL Server are separate physical volumes for the system and data disks using RAID 1+0.

I will note @Spörri's recommendation also: first, check to see if the RAID array is degraded because of a failed disk.