Tuesday, March 31, 2020

Incorrect syntax near '(' - sys.dm_db_index_physical_stats

So I have this script that I run often to check index fragmentation percentage with SQL Server tables.  I've run it a million times without error, but suddenly today it fails with this message:

     Msg 102, Level 15, State 1, Line 24
      Incorrect syntax near '('.

Even weirder, is that I ran it successfully on one of the databases on the server, but it failed for every other one I tried -- same server.  Very odd. 

SELECT
       @@SERVERNAME ServerName,
       DB_NAME() DatabaseName,
       dbs.[name] [Schema],
       dbtables.[name] [Table],
       dbi.[name] [Index],
       idx.avg_fragmentation_in_percent [%Fragmented],
       idx.page_count [#Pages]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) idx
       INNER JOIN sys.tables dbtables
         ON dbtables.[object_id] = idx.[object_id] INNER JOIN sys.schemas dbs
           ON dbtables.[schema_id] = dbs.[schema_id] INNER JOIN sys.indexes dbi
                ON dbi.[object_id] = idx.[object_id]
                AND idx.index_id = dbi.index_id
WHERE
       idx.database_id = DB_ID()
       AND idx.avg_fragmentation_in_percent  > 30
       AND idx.page_count > 1000
ORDER BY
       idx.avg_fragmentation_in_percent DESC,
       idx.page_count DESC


A lot of googling, and I found that it was because due to a conflicting compatibility level.  My server was 2012, but the database compatibility level was 2000... which does not use the DB_ID() parameter.  My workaround was to use a variable for the @db_id instead, like this:


USE dbname

DECLARE @db_id INT
SELECT @db_id = DB_ID()

SELECT
       @@SERVERNAME ServerName,
       DB_NAME() DatabaseName,
       dbs.[name] [Schema],
       dbtables.[name] [Table],
       dbi.[name] [Index],
       idx.avg_fragmentation_in_percent [%Fragmented],
       idx.page_count [#Pages]
FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL, NULL, NULL) idx
       INNER JOIN sys.tables dbtables
         ON dbtables.[object_id] = idx.[object_id] INNER JOIN sys.schemas dbs
           ON dbtables.[schema_id] = dbs.[schema_id] INNER JOIN sys.indexes dbi
                ON dbi.[object_id] = idx.[object_id]
                AND idx.index_id = dbi.index_id
WHERE
       idx.database_id = DB_ID()
       AND idx.avg_fragmentation_in_percent  > 30
       AND idx.page_count > 1000
ORDER BY
       idx.avg_fragmentation_in_percent DESC,
       idx.page_count DESC

Completed successfully this time...





No comments:

Post a Comment