Ran into a similar situation some years ago. After trying everything else, we stopped and re-started the SQL Server service and problem solved.

Exceptionally busy next few days coming, but I did rebuild this morning, set a trace, and did check once to find 0.04% fragmentation - not the usual leap to 66.-whatever%...

Confirmed: 0 for index level, In-Row-Data, and trigger generated the expected email for each of an actual Insert, Update and Delete.

FWIW: This is not a end of the world scenario...

So... since the trigger isn't catching it, is my next step Extended Events?

It's coming from Table - Indexes - Reorganize. The underlying source is sys.dm_db_index_physical_stats, I believe.

After load - create index, as well as after rebuild or reorg,  avg_fragmentation_in_percent = 0

So, the trigger has been in place, but has not fired. It's simple: On I/U/D, send me an email with the user_name().

I rebuilt the index on Friday morning, this morning,...

Couple of days later. Wish I had the query handy, but it's on my other workstation, the one with the fried HD from a power blip last Friday. Good thing...

Won't be around for long, methinks.

Also, still on 2008, and XE was not friendly in the slightest back ...er... now 🙂 That said, can't wait to start playing with it...

As mentioned, the index is created post-load.

Thanks for taking the time to respond. Trigger, here I come!

I'll let you know what I find.

Thanks again

P

Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.

Could...

If you have space on prod, restore it to  second DB on prod server, remove unneeded tables/content, then back that up and restore to dev.

Thank you all.

This was a purely educational matter.

I share your concerns re: cursors and dynamic SQL, but sometimes there is no other way.

At any rate, this report will likely run...

I could have been clearer: the logical reads are on the target of the INSERT.

It's an INSERT #Action/EXECUTE dbo.sp_executeSQL, inside a cursor. Could it be because it's dynamic SQL?

I SET...

• This reply was modified 2 years, 10 months ago by  schleep.

I need to call the sprocs with named parameters, so I ended up just building a command in the cursor declaration.

I think there was always going to be some hard-code,...

So I'm running a bunch of reports through a cursor. Different DBs, different subsets of the same list of reports.

A couple of the reports have additional parameters. I was hoping...