July 8, 2014 at 9:33 pm
Hi guys,
I'm running below script to get the list of fragmented indexes but I'm getting this error:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.
SQL Server version is 2005.
Any ideas? Thanks!
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
July 8, 2014 at 10:40 pm
This is most likely because the compatibility level for the database is less than 2005(90), nothing wrong with this code.
😎
July 8, 2014 at 11:47 pm
Thank you, Yes, Compat level is 2000. How can I see average fragmentation on my indexes without changing the compat level of the database?
Thanks
July 9, 2014 at 12:13 am
whitesql (7/8/2014)
Thank you, Yes, Compat level is 2000. How can I see average fragmentation on my indexes without changing the compat level of the database?Thanks
2K doesn't like the db_id function as an input parameter, just replace it with a variable.
😎
declare @db_id int
select @db_id = DB_ID()
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') AS indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
July 9, 2014 at 12:50 am
Thank you! :hehe:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply