SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS

• LightShip

  SSC-Addicted

  Points: 421

  More actions

  February 12, 2015 at 6:04 am

  #295325

  I am seeing unusual behaviour of this or maybe my understanding is warped.

  Depending on where I run the query from - regardless of the DBID specified, it runs in the local database. It does however validate the the DBID exists (which is why the 19000 fails...)

  Am I being a muppet and missing something fundamental in my syntax?

  I am expecting to only see 2 pages returned, regardless of the DB I am using when the TSQL is run.

  When it is run in master, the NULL response to the objectid call for the table is causing the DMF to return all the pages in use by all objects in the local database. This in spite of passing the DB_ID of 9 to the query (which is the db_id for corruptme database).

  Queries I ran (as a sidebar piece of curiosity during an article on how to corrupt a test database on purpose by Jes[/Url]

  select @@Version as [SQL Version]

  go

  set nocount on

  go

  use master

  go

  select count(allocated_page_page_id) as [Count pages from DB 19000 - doesnt exist]

  from

  sys.dm_db_database_page_allocations (19000,object_id ('purchasing.shipmethod'),1,1,'DETAILED'); -- DB ID doesn't exist

  go

  select count(allocated_page_page_id) as [Count pages from DB id CorruptMe - TSQL issued from Master DB]

  from

  sys.dm_db_database_page_allocations (9,object_id ('purchasing.shipmethod'),1,1,'DETAILED');

  go

  use [CorruptMe]

  go

  select count(allocated_page_page_id) as [Count pages from DB id CorruptMe - TSQL issued from CorruptMe DB]

  from

  sys.dm_db_database_page_allocations (DB_ID('corruptme'),object_id ('purchasing.shipmethod'),1,1,'DETAILED');

  go

  SQL Version

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

  Oct 19 2012 13:38:57

  Copyright (c) Microsoft Corporation

  Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  Count pages from DB 19000 - doesnt exist

  ----------------------------------------

  Msg 913, Level 16, State 8, Line 1

  Could not find database ID 19000. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

  Count pages from DB id CorruptMe - TSQL issued from Master DB

  -------------------------------------------------------------

  24172

  Count pages from DB id CorruptMe - TSQL issued from CorruptMe DB

  ----------------------------------------------------------------

  2