Thanks.

that is under way, which takes while to do that.. any other solution?

it was blocking for more then 12 hours.. and which crazy that create triple thread block and re-org blocked 2 other process in chain.. since having large table i cannot...

Thanks for the quick suggestion. i will check that when it run next time. but is there any idea why re-org should block the( it had schema lock), i was...

i was using this

SELECT

DB_NAME(er.[database_id]) [DatabaseName]

,er.start_time

,sp.memusage

,sp.cpu

,es.host_name

,es.logical_reads

,es.lock_timeout

,es.reads

,sp.loginame

,es.row_count

,er.[session_id] AS [SessionID]

,er.[command] AS [CommandType]

,est.[text] [StatementText]

,er.[status] AS [Status]

,er.blocking_session_id

,er.[last_wait_type] [LastWait]

,er.[wait_resource] [CurrentWait]

FROM sys.dm_exec_requests AS er

INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]

OUTER APPLY sys.dm_exec_sql_text(er.[sql_handle])...

i did use it but statement shows NULL

Thanks, appreciate for that response. that did not give any return.. question is that 'DB STARTUP' command get blocked every now and then for second or two. is this...

Thanks for the response. i am also looking into the secondary, there is frequent block is happening to the command DB STARTUP. IS that an issue?

Thanks, i will have to test on that..

great, thats the plan to do next for the partition.. quick question though since we have minimum down time and if we like create db with partition in multiple file...

i did not get it 'Then look at the rebuild options, in particular parallelism.' we don't have partitions.

Thanks both for the Reply,it is sql server 2012 enterprise edition sp1

Yea, but if we don't have option to do re-build.. what end up happen if this large table gets more then 40% fragment, trying to see the better option if...

last time as i remember we were able to rebuild successfully to that large table with online on tooks about 20 hr...

it was 28% fragmented