SSMS reports are one of the best ways to pull some great deal of Server level or Database level information within SQLServer.
Today I was running the ‘Disk Usage’ report (This one is my favorite) for one of the database and immediately got this error!
Error – Index (zero based) must be greater than or equal to zero and less than the size of argument list!
Well that’s one really cool error message to get when you are seriously working on something important!
Immediately I checked to see if the same report is causing issues for other databases which were available on the same instance.
The report was working great for all other databases except one which gave the error.
All the 4 ‘Disk Usage’ reports where having issues for that particular database, and I decided to compare this database with one for which the report was running fine.
Eventually the culprit was found. The database was having compatibility level 80 (SQL 2000) and was residing on a SQL Server 2005 SP4 instance. SSMS which I used to run reports was of version 10.50.2500 (2008 R2 SP1).
There are multiple T-SQL statements which are ran to pull this report,and this can be tracked by running a SQL-Profiler(Again, with caution for a production environment)
Changing the compatibility to 90 fixed the issue and the reports were running as expected.
Note - Changing the compatibility needs to be reviewed properly with the application owners as it can bring unexpected results. Proper testing and approval needs to be taken.
Yet another reason to find all the databases which are running with lower compatibility and push application owners to fix their code where ever its required and move forward with latest compatibility levels.
For SQL Server 2012 compatibility level 80 is not supported, hence you won’t face these type of compatibility related issues anymore.