Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed SQL Server 2012 RC0 on my laptop recently and I was trying a demo which illustrates how shrink database will cause fragmentation. I created a table, a clustered index on the identity column and inserted a few records and then issued the below to check fragmentation:

select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('databasename'), 
  OBJECT_ID('tablename'), 1, NULL, 'limited')

This is the error message I get:

Could not find table or object ID 2139154666. Check system catalog.

I did a select * from sysobjects and the Object exists with that objectID. Why does SQL Server think that it doesn't exist? What am I doing wrong?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent 
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.

share|improve this answer
    
Tried this, it says "insufficient number of arguments were supplied" –  yogirk Feb 20 '12 at 13:55
    
@yogirk - I edited, try it again. I took out the DB_ID but it needs to be in there –  JNK Feb 20 '12 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.