sys.dm_db_index_physical_stats DMV

Question

Post reply

sys.dm_db_index_physical_stats DMV

Vicki Peters

SSCommitted

Points: 1866
More actions
August 16, 2011 at 12:45 pm

#145963

Hi. I am hoping someone is familiar with this DMV. We do a biweekly rebuild of the indexes on one of our databases. We have an overnight job that calls a custom procedure. The procedure runs the following SELECT statement to gather the tables/views on which we want to rebuild the indexes:
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 8.0
AND index_id > 0
We recently migrated to a new server and ever since then the job has been failing on this database. The error is that the index cannot be created on object 'dbo.objectname' because the object is not a user table or view.
This is true...the object is a stored procedure. For the life of me, I cannot figure out why this view is picking up this stored procedure when it never did before. Does anyone know what could be causing this/how to fix it?
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Vicki I'm pretty sure the issue is that the same object_id exists in multiple databases....

that view returns multiple database results, not just results from a single database.

change that query to this one, which has more detail, and you'll see that the object in the query exists in a different database, and it's not a stored proc at all.

SELECT

DB_NAME(database_id) AS DB,

OBJECT_NAME(object_id,database_id) AS TheObject,

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 8.0

AND index_id > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vicki Peters SSCommitted Points: 1866 More actions · Answer 2

Thanks for the input. I am running the query now and of course it's taking a while. In the meantime, however, it's probably worth noting that I had already done:

SELECT * FROM sys.objects WHERE name = 'CM_Insert_ContAddrNumber' and I only got back one record for this object, and it's a stored procedure. I would've expected to get multiple records back for that object if it existed in more than one database.

Lowell SSC Guru Points: 323518 More actions · Answer 3

no...SELECT * FROM sys.objects selected from the current database.

sp_msForEachDB 'select

''?'' as DbName,

name

,object_id from ?.sys.objects

where object_id = 2073058421 '

would show you all the objects that , buy coincidence, have the same object_id accross multiple databases for a given, specific object_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vicki Peters SSCommitted Points: 1866 More actions · Answer 4

Oh! Okay, running that did show me the other object and it is indeed on another database.

I see exactly what happened now. When these databases were created on the new server they were given new database IDs.

The custom procedures were created on each database and are hard-coded to reference the database ID.

I need to update each procedure to reference the correct database ID. (Or make it dynamic.)

Thanks for your help!