Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: sys.dm_db_index_physical_stats doesn't work on user db's

    Using sys.dm_db_index_physical_stats against a user db
    doesn't work only works against the system db's... anyone know why... I'm not sure....
    Error against user db's
    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near '('.

    SELECT
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
    --INTO
    --work_to_do
    FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0


    any suggestions would be greatly appreciated

  2. #2
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    I can get it to work on other user db's on other servers... just not sure what I'm missing....

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I will bet that the compatibility level on the databases that do not work is set to less than 90.
    Code:
    sp_dbcmptlevel databasename

  4. #4
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    Quote Originally Posted by MCrowley
    I will bet that the compatibility level on the databases that do not work is set to less than 90.
    Code:
    sp_dbcmptlevel databasename
    MCrowley that is exactly what it was.... thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •