June 5, 2010 at 7:20 am
I am running sql 2005 and have a large database containing about 50 tables. Recently this database had an issue and went into recovery automatically.
Since then the reporting from this database has been troublesome and I have tracked it down to a specific table that when opening it shows a grey screen or an error message stating I either do not have permission or the object does not exist.
I can delete the table reboot the server and then recreate the table but the issue persists. how ever changing the name resolves the issue.
Can anyone tell me what is wrong and how to fix it?
Many thanks
June 7, 2010 at 12:11 pm
Hello,
The table name is tbl_merchandising_profiles_merlive.
This has on average 1.2 to 1.3 miliion rows.
I open this table by right clicking on the table and selecting open table.
The table has 7 columns.
There is one primary key that covers 3 columns (uniqueness).
There are two further non clustered indexes that were sugessted by the database tuning advisor.
Everything was working fine until the database went into recovery.
Since then renaming the table and then opening it did allow me to open it and view the data as described above.
When renaming it back to the original name the problem reocurrs. This is despite deleting the table and recreating it.
This table is basically truncated each day and then the data refreshed.
I have spent alot of time googling but can not find anything related to this issue. I have run integrity checks, I have read various things about caching and thought may be this is it and am also thinking that there must some data retained in the system tables that could be causing this.
Any help on this is much appreciated.
June 7, 2010 at 7:13 pm
Hi,
Can you select from this table from Query Analyzer or SQl Server management Studio using this query?
select top 100 * from tbl_merchandising_profiles_merlive
June 8, 2010 at 1:22 am
First you need to check your database consistency and allocation , run DBCC CheckDB command for the particular database and do post what it shows.
Regards
Ashish Gupta
June 8, 2010 at 5:42 am
Hi
The query will return the top 100 and also a select all query reports back data with no issue.
Out of interest, if it had not returned the top 100 rows what would that be inidicating to you and what would you do next?
Many Thanks
June 8, 2010 at 6:30 am
Selecting all records from large tables didn't work for me in past as well when I opened tables using right click. So the top query is just simple check to make sure that table is accessible. You had an error message
stating I either do not have permission or the object does not exist
, but this simple query shows that there is nothing to do with permissions.
Does this table have any indexes? Is it possible that index has been dropped when table has been recreated? If the table didn't have any indexes this is time to create one.
June 8, 2010 at 8:51 am
hi
The permission issue only arose when trying to open the table via right click.
I had no problem access the tables via queries but since recreating them with different names there has been no issues.
Index wise I don't believe there to be an issue as I created the table and all indexes exactly as they were before. The only difference is that I renamed the table to a different name but kept the indexes with their original names.
Also during my trials I did also drop indexes on the off chance that they were the issue but to no avail.
The table has indexes created but when I first started building this data store it did not have any indexes and it did not affect performance noticeably (although my expectations have grown since). In everything I have done so far I have never encountered this issue as the tables have always opened and displayed data no matter the row count.
Regards
Guy
June 8, 2010 at 10:35 am
After running dbbc checkdb the following was displayed:
DBCC results for 'APOLLO'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1445 rows in 15 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 179 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 194 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1445 rows in 18 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 179 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 194 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 20 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 126 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 167 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1033 rows in 21 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 585 rows in 18 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 1118 rows in 12 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 610 rows in 325 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 636 rows in 4 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'TBL_MERCHANDISING_PROFILE_LEVELS'.
There are 1204113 rows in 7479 pages for object "TBL_MERCHANDISING_PROFILE_LEVELS".
DBCC results for 'TBL_STATIC_DATA_CALENDAR'.
There are 3488 rows in 37 pages for object "TBL_STATIC_DATA_CALENDAR".
DBCC results for 'TBL_STATIC_DATA_LOCATIONS'.
There are 58 rows in 2 pages for object "TBL_STATIC_DATA_LOCATIONS".
DBCC results for 'TBL_STOCK_DATA_ASH'.
There are 5634751 rows in 46957 pages for object "TBL_STOCK_DATA_ASH".
DBCC results for 'TBL_STATIC_DATA_MMGS'.
There are 2366 rows in 80 pages for object "TBL_STATIC_DATA_MMGS".
DBCC results for 'TBL_STATIC_DATA_SUPPLIER'.
There are 2811 rows in 107 pages for object "TBL_STATIC_DATA_SUPPLIER".
DBCC results for 'TBL_STATIC_DATA_PRODUCT_MOVEMENTS_BUSINESS_FUNCTIONS'.
There are 46 rows in 1 pages for object "TBL_STATIC_DATA_PRODUCT_MOVEMENTS_BUSINESS_FUNCTIONS".
DBCC results for 'TBL_STATIC_DATA_PRODUCT_TAX'.
There are 182530 rows in 923 pages for object "TBL_STATIC_DATA_PRODUCT_TAX".
DBCC results for 'TBL_HISTORY_PRODUCT_MOVEMENTS_PROMOTIONS'.
There are 0 rows in 0 pages for object "TBL_HISTORY_PRODUCT_MOVEMENTS_PROMOTIONS".
DBCC results for 'TBL_STATIC_DATA_PRODUCT_CATS'.
There are 1963532 rows in 8446 pages for object "TBL_STATIC_DATA_PRODUCT_CATS".
DBCC results for 'TBL_HISTORY_PRODUCT_MOVEMENTS_PROMOTIONS_ARCHIVE'.
There are 24089829 rows in 71252 pages for object "TBL_HISTORY_PRODUCT_MOVEMENTS_PROMOTIONS_ARCHIVE".
DBCC results for 'TBL_STATIC_DATA_SUPPLIER_CATS'.
There are 10799 rows in 64 pages for object "TBL_STATIC_DATA_SUPPLIER_CATS".
DBCC results for 'TBL_HISTORY_VALID_TRANS_HDR'.
There are 4027325 rows in 42175 pages for object "TBL_HISTORY_VALID_TRANS_HDR".
DBCC results for 'TBL_HISTORY_VALID_TRANS_ITEMS'.
There are 21417903 rows in 451914 pages for object "TBL_HISTORY_VALID_TRANS_ITEMS".
DBCC results for 'TBL_STATIC_DATA_RLTD_PRD_GRP_ITEMS'.
There are 3739 rows in 15 pages for object "TBL_STATIC_DATA_RLTD_PRD_GRP_ITEMS".
DBCC results for 'TBL_STATIC_DATA_RLTD_PRD_GRPS'.
There are 1266 rows in 4 pages for object "TBL_STATIC_DATA_RLTD_PRD_GRPS".
DBCC results for 'TBL_HISTORY_VALID_TRANS_ITEMS_ARCHIVE'.
There are 107401990 rows in 1681526 pages for object "TBL_HISTORY_VALID_TRANS_ITEMS_ARCHIVE".
DBCC results for 'TBL_RECONCILIATION_VALID_TRANS'.
There are 0 rows in 0 pages for object "TBL_RECONCILIATION_VALID_TRANS".
DBCC results for 'TBL_STATIC_DATA_REASON_CODES'.
There are 148 rows in 1 pages for object "TBL_STATIC_DATA_REASON_CODES".
DBCC results for 'TBL_STOCK_DATA_CLOSING_STOCK'.
There are 0 rows in 0 pages for object "TBL_STOCK_DATA_CLOSING_STOCK_DATAMART".
DBCC results for 'TBL_MERCHANDISING_PROFILE_LEVELS_ARCHIVE'.
There are 524804761 rows in 3267273 pages for object "TBL_MERCHANDISING_PROFILE_LEVELS_ARCHIVE".
DBCC results for 'TBL_HISTORY_VALID_TRANS_HDR_ARCHIVE'.
There are 26472316 rows in 304194 pages for object "TBL_HISTORY_VALID_TRANS_HDR_ARCHIVE".
DBCC results for 'TBL_HISTORY_OVERNIGHT'.
There are 2696 rows in 33 pages for object "TBL_HISTORY_OVERNIGHT".
DBCC results for 'TBL_STATIC_DATA_PRODUCT'.
There are 182530 rows in 7007 pages for object "TBL_STATIC_DATA_PRODUCT".
DBCC results for 'TBL_HISTORY_SALES_POLLING'.
There are 1389 rows in 7 pages for object "TBL_HISTORY_SALES_POLLING".
DBCC results for 'TBL_STOCK_DATA_BOASH'.
There are 5636581 rows in 93944 pages for object "TBL_STOCK_DATA_BOASH".
DBCC results for 'TBL_HISTORY_PRODUCT_MOVEMENTS_ARCHIVE'.
There are 275229851 rows in 11329961 pages for object "TBL_HISTORY_PRODUCT_MOVEMENTS_ARCHIVE".
DBCC results for 'TBL_HISTORY_PRODUCT_MOVEMENTS'.
There are 0 rows in 0 pages for object "TBL_HISTORY_PRODUCT_MOVEMENTS".
DBCC results for 'TBL_DATA_WAREHOUSE_STATUS'.
There are 34 rows in 2 pages for object "TBL_DATA_WAREHOUSE_STATUS".
DBCC results for 'TBL_HISTORY_AGG_WK_LOC_PRD_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_WK_LOC_PRD_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_WK_LOC_PRD_SALES'.
There are 65214416 rows in 647192 pages for object "TBL_HISTORY_AGG_WK_LOC_PRD_SALES".
DBCC results for 'TBL_HISTORY_AGG_DLY_LOC_PRD_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_DLY_LOC_PRD_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_DLY_LOC_PRD_SALES'.
There are 117227972 rows in 1595107 pages for object "TBL_HISTORY_AGG_DLY_LOC_PRD_SALES".
DBCC results for 'TBL_HISTORY_AGG_DLY_LOC_MMG_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_DLY_LOC_MMG_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_DLY_LOC_MMG_SALES'.
There are 31949056 rows in 286165 pages for object "TBL_HISTORY_AGG_DLY_LOC_MMG_SALES".
DBCC results for 'TBL_HISTORY_AGG_WK_LOC_MMG_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_WK_LOC_MMG_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_WK_LOC_MMG_SALES'.
There are 9523389 rows in 84484 pages for object "TBL_HISTORY_AGG_WK_LOC_MMG_SALES".
DBCC results for 'TBL_HISTORY_AGG_PERIOD_LOC_PRD_SALES'.
There are 30424183 rows in 395717 pages for object "TBL_HISTORY_AGG_PERIOD_LOC_PRD_SALES".
DBCC results for 'TBL_HISTORY_AGG_PERIOD_LOC_PRD_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_PERIOD_LOC_PRD_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_PERIOD_LOC_MMG_SALES'.
There are 2031317 rows in 23033 pages for object "TBL_HISTORY_AGG_PERIOD_LOC_MMG_SALES".
DBCC results for 'TBL_HISTORY_AGG_PERIOD_LOC_MMG_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_PERIOD_LOC_MMG_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_YEAR_LOC_MMG_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_YEAR_LOC_MMG_SALES_TEMP".
DBCC results for 'TBL_HISTORY_AGG_YEAR_LOC_MMG_SALES'.
There are 275694 rows in 2993 pages for object "TBL_HISTORY_AGG_YEAR_LOC_MMG_SALES".
DBCC results for 'TBL_HISTORY_AGG_YEAR_LOC_PRD_SALES'.
There are 7048637 rows in 87430 pages for object "TBL_HISTORY_AGG_YEAR_LOC_PRD_SALES".
DBCC results for 'TBL_HISTORY_AGG_YEAR_LOC_PRD_SALES_TEMP'.
There are 0 rows in 0 pages for object "TBL_HISTORY_AGG_YEAR_LOC_PRD_SALES_TEMP".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'TBL_HISTORY_PRODUCT_MOVEMENTS_LTST_MVMT_DATE'.
There are 10272437 rows in 89506 pages for object "TBL_HISTORY_PRODUCT_MOVEMENTS_LTST_MVMT_DATE".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'TBL_STOCK_DATA_BOASH_OLD'.
There are 5635554 rows in 93926 pages for object "TBL_STOCK_DATA_BOASH_OLD".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'APOLLO'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 9, 2010 at 9:15 am
Run the Checkdb command as
DBCC CHECKDB
WITH NO_INFOMSGS, ALL_ERRORMSGS.
So that it displays only the error messages.
Try restoring the DB to other location and see if the same issue persists with the original table.
Pavan.
June 10, 2010 at 8:30 am
DBCC CHECKDB
WITH NO_INFOMSGS, ALL_ERRORMSGS.
This compelted with no issues reported. It simply states Command(s) completed successfully.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply