August 11, 2013 at 7:54 am
Hi all,
I have a very strange case (at least for me). Job which was running smoothly for a long time (like 2 years) about 30-40 min now takes hours or even days if ever succeeded.
First the errors was on Linked servers ( there is a query which selects from 2 remote sources). I run this query manually without any problem.
But now I am getting this error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105
Could not locate statistics '...' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Each time it complains on different columns. Those columns belong to remote tables and not even in a select list.
I run update statistics on all of remote tables and it didn't help at all. I am still getting the same error. I do not understand where DBCC execution comes from. SP doesn't have it, I checked. Is it some setting can be changed to avoid it?
Thanks.
August 12, 2013 at 7:47 am
I would really appreciate any suggestions.
August 12, 2013 at 7:57 am
This really ins't much to go on. Tell me...what are you doing in the procedure sp_table_statistics2_rowset? Specifically, line 105 is the source of the error.
August 12, 2013 at 7:59 am
Nothing. I didn't call it. This is the error my SP finishes with.
August 12, 2013 at 8:04 am
So, to summarize:
1. You have a database job that calls your stored procedure.
2. Your stored procedure does not call the stored procedure named sp_table_statistics2_rowset.
3. The job runs and complains about an error raised in sp_table_statistics2_rowset on line 105.
Is this correct? The reason I'm asking is because it doesn't make sense. Then again, I could just be missing something.
Does your procedure call any other procedures? Perhaps one of them calls sp_table_statistics2_rowset.
August 12, 2013 at 9:14 am
all your assumptions are correct. I agree, it doesn't make any sense. I tried to run t-sql code from my SP manually and this error occurs after it selects from remote server through the linked server. I catch the error, run update statistics on that particular remote table and resemble. It runs further until the next error. i run update statistics on all tables in remote DB, but it didn't help. On some tables it still gives an error. We have big volume inserted into some tables, but again, this job was running for 2 years and this started recently, on Friday.
August 12, 2013 at 9:33 am
I did some research (via Bing) and found an interesting article about this very topic: http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/. It looks like it's based in the permissions of the user executing the query on the linked server. Please have a look - this may be your issue.
August 12, 2013 at 10:38 am
Thank you very much!
August 12, 2013 at 11:04 am
No problem - glad I could help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply