Sounds good, thanks much for your help Jacob.

--
Scott

OK, I used your query and mystery solved ( mine walked only user DBs ). As I suspected...

database_name    table_name    forwarded_fetch_count
tempdb    #ACB65264    19660418
tempdb    #B3933082    6790229
tempdb    #A08DAD08    3121187
tempdb    #B0FAEBA2    2248807
tempdb    #ADAA769D    1982640
tempdb    #A8C43DCB    814180
etc.

Only one...

Thank you, I looked at that one but didn't see that column. The largest value is 2852 for all tables but the counter is way over that even in just...

Seems strange, it is probably something the DBA and/or Network engineer should be investigating instead of coding around it because it will only get worse when you scale out.

I don't...

Thanks. I never did figure it out. I was observing this in our SQL Spotlight monitoring tool, I clicked into once and it changed to tempdb.<file> something or other. It...

Not that I recall. Management kicked back on the idea of enabling resource gov so I stopped pursuing it. I'm sure it will come up again though.

Nevermind, I found it. The lock timeout was being reset by a nested function that doing the actual execute sql. Argh.

Thanks again.

--

Scott

I was iterating the indexes of the database and executing the alter index with dynamic sql. It appears the set statement does not take affect unless you actually execute as...

Ugh. Thanks, will keep this in mind.

That function worked like a charm for me thank you.

Thanks. I know that the GO can be removed in the example. The actual code I have will need the GO statement because it is SQL creating objects and such,...

Ok, thanks much.

Scott

I should know better by now than to post a question on a friday afternoon...:-)

The database is the publisher of logshipping. CDC is not enabled. log_reuse_wait_desc is LOG_BACKUP.

Thanks

Scott

The database is the publisher of logshipping. CDC is not enabled. log_reuse_wait_desc is LOG_BACKUP.

Thanks

Scott