Thank you all for comments.

Cheers, Jacek

The point is, what produce more page swaps: reorganize or rebuild?

According to Microsoft "Reorganizing an index...

Table is a 13GB, fragmentation 20%,

Are you rebuilding with sort_in_tempdb=ON, and then make comparison with the Reorganizing (reorganizing is...

TheSQLGuru (10/19/2014)

---

Most likely cause is simply that you are reorging wth too much fragmentation. The page swapping can CRUSH you (and the tlog and data files with writes) when...

Grant Fritchey (10/19/2014)

---

Contention for resources? Not sure without more information. Have you looked at the blocks and wait resources of the process while it's running? That's where I'd start in...

Kevin, index_1 compromise columns...

Thank you very much Gail for clearing the issue.

Jacek

My approach is to check the statistics are updated. If Yes, I don not bother anymore as...

Gail,

Please find attached the files.

One is for index_1 (old) and one for index_2 (filtered).

Thanks in advance.

I was trying many options.

Finally I discovered interesting solution. It does not make sense for me but works:

1. I have created additional filtered index (index_2)

2. checked estimated and actual numbers...

By the way, I found shrinking against SSMS always failed in my case.

I do not why, but following script always works for me (it shrinks data file, too):

declare @dbname...

It is worthy to check CDC status in a few areas.

Please use following command to make sure everything is configured:

EXECUTE sys.sp_cdc_help_change_data_capture;-- Returning change data capture configuration information for all...

You are right GilaMonster. The difference is in...

Hi,

Try to remove cdc job, backup log file and recreate cdc job. Check BOL to find t-sql command recreating the cdc job.

Jacek

Is it simple model? Simple model is without log file.

Otherwise backup log file just before srinking.

Jacek