locked
Cannot find the object % because it does not exist or you do not have permissions RRS feed

  • Question

  • when i query for fragmented indexes in databsae(lets say adventureworks), i got more & more indexresults with fragmentation percentage . But when I try to reorganize/rebuild those results i got the below error

    """Cannot find the object xxxxxxxxxxxx  because it does not exist or you do not have permissions."""

    after I goolged , i navigated to the tables in ssms & performed index defragment operation. what about the other resutls of indexes......

    if they are not exist in database, how the results are coming for   sys.dm_db_index_physical_stats(14,null,null,null,null).

    Thanks in advance


    -<


    • Edited by sqldbarocks Sunday, March 4, 2012 1:37 PM just nothing
    Sunday, March 4, 2012 1:35 PM

Answers

All replies

  • To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

    SELECT db_name(database_id) AS DatabaseName,
    OBJECT_NAME(i.object_id) AS TableName 
    ,
    i.name AS TableIndexName 
    ,
    phystat.avg_fragmentation_in_percent


    FROM
    sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i 
    ON i.object_id = phystat.object_id 
    AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40 
    and page_count>=1000


    --single index
    ALTER INDEX IX_iname ON tblname
    REBUILD;
    GO
    --all indexes on the table

    ALTER INDEX ALL ON tbl
    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
                  STATISTICS_NORECOMPUTE = ON);
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Lola Wang Tuesday, March 6, 2012 7:40 AM
    Sunday, March 4, 2012 1:41 PM
  • Hi sqldbarocks,

    More details for your reference:
    http://scottstoecker.wordpress.com/2011/02/18/sql-reporting-services-cannot-find-the-object-error/
    http://www.sql-server-performance.com/2007/cannot-find-object/

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.

    • Marked as answer by Lola Wang Tuesday, March 27, 2012 9:57 AM
    Tuesday, March 6, 2012 7:41 AM