Killing is not an optimal solution it should be last weapon to use.

definitely Tempdb settings adjustment required here.may be multiple files reuired. do check the percentage of used and free of files here for tempdb.

have you hard coded this update stmt in job or using the maintenance plan for it ?

1 .statistics out of date for every table.

2. non-sarge-able column use which causing the index scan here.

3. function use also affecting here.

4. check if you have properly used the...

OP can you please post the related index definition too?

almost 4 years old post 😀

see this link http://sqlserverpedia.com/wiki/Configuring_Database_Files_for_Optimal_Perfomance

aaron.reese (5/1/2012)

---

I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC)...

Ramkumar (LivingForSQLServer) (5/1/2012)

---

My few cents here.

2. isolate the db having the pain. sys.dm_io_virtual_file_stats dmv will help you to find that.

3. identify painful objects in key databases. sql server sys.dm_db_index*...

can u tell us the hardware configuration of server box? no if databases ? size of databases? disks ? and is any reporting intsance sitting on same box?

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on dv file path

Another...

@kinston,

i liked you reply it helped me but one doubt ?

do we really need

case when c.name LIKE '%[0-9]' then '' else c.name end -- needed to sort "count_stub"...

Expected output

means i need all values(having numbers as their suffix) in a sequence.

my order by clause is not working as expected :crying:

Thanks Lynn but my actual requirement is declare @t table (name nvarchar(50))

insert into @t

select 'count_1'

union select 'acct_id'

union select 'count_19'

union select 'count_2'

union select 'count_29'

union select 'count_3'

union select 'count_30'

union select 'count_100'

union select...

it seems that u don't require the space actually. Then y are you trying to reclaim the space ? additionally if you really wanted to reclaim it you should have...