Menu Bar

Saturday, September 17, 2011

DMV sys.dm_db_index_physical_stats is broken ?

"Damage Report!" - Assorted Star Fleet Captains in just about every form of Star Trek

There's this little database I have that sits in a corner and is mostly forgotten. It runs and does its job and rarely causes me any problems - and consequently tends to get overlooked sometimes.

This month, during my monthly maintenance window I thought "Hmmm, maybe I should do a little index maintenance on this sucker".

So I pull out my Index Defragmentation Scripts and throw it into SSMS and hit Execute and it says:

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

What the heck is that ?

It's complaining about the DB_ID() parameter in the call to the DM function.

So I went and talked to my best buddy Google and found this gem.

What I didn't realize is that the version of the software for the application this database supports is pretty old and out of date. It requires that SQL Server's compatibility mode on this instance be 80. This function doesn't like DB_ID() as a parameter in that compatibility mode.

I'm sure that the solutions posted on that thread work, but I wasn't going to toy with it. I executed a quick SELECT DB_ID(), found the database_id, put that integer in place as the parameter and away we went!