Post reply

Index fragmentation

  • April 27, 2011 at 11:39 pm

    #236396

    Hi All,

    I am using DMV's for checking the fragmentation of index. At below line i am getting the error :

    Msg 102, Level 15, State 1, Line 8

    Incorrect syntax near '('.

    sys.[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'Detailed') s.

    the same script run for other db's and it gives result but few user db's it gives the above error. I have sysadmin roles. Not able to figureout what is the problem.

    Please guide

  • April 27, 2011 at 11:57 pm

    #1317043

    Compatibility level is 80.

    Run from master and pass the DB_id in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 28, 2011 at 12:19 am

    #1317047

    Thanks for your reply. The DB compatibility level was 80 as you said. Now to build the indexses shall i use alter index command or dbcc dbreindex for these databases please guide

  • April 28, 2011 at 1:40 am

    #1317070

    Alter index REbuild. Compat level doesn't change that DBCC DBREINDEX is deprecated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 28, 2011 at 6:33 am

    #1317201

    dbcc dbreindex will work but it will be removed in the future version of the sql server.

    Hence advisable to use Alter index from sql server 2005 onwards as mention by Gila.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • April 28, 2011 at 6:51 am

    #1317215

    Thanks to All for your valuable replies

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply