Syntax issue on AzureSQLDB

Question

Hi Team,

I am in middle of troubleshooting Deadlock but getting below error while running below query: i am passing values waitresource="OBJECT: 5:756197744:0 "

SELECT * --OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,  
--        OBJECT_NAME(pg.object_id) AS object_name,  
--        i.name AS index_name,  
--        p.partition_number  
 FROM sys.dm_db_page_info (DB_ID(),5, 756197744, default) AS pg  
 INNER JOIN sys.indexes AS i  
 ON pg.object_id = i.object_id  
    AND  
    pg.index_id = i.index_id  
 INNER JOIN sys.partitions AS p  
 ON pg.partition_id = p.partition_id;  
  

ERROR:
Parameter 2 is incorrect for this statement.

Answer 1

Hi,

The error is telling you that 5 is not a valid file_id for the database. Were you connected to the correct database when you ran this? Below query will show you the file_ids:

select * from sys.database_files

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-page-info-transact-sql?view=sql-server-ver16

Answer 2

FileId is 1 on Azure SQL. Please make the adjustment. See example below with sample database AdventureWorksLT.

 SELECT    
 OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,  
         OBJECT_NAME(pg.object_id) AS object_name,  
         i.name AS index_name,  
         p.partition_number  
  FROM sys.dm_db_page_info (DB_ID(),1, 10, default) AS pg  
  INNER JOIN sys.indexes AS i  
  ON pg.object_id = i.object_id  
     AND  
     pg.index_id = i.index_id  
  INNER JOIN sys.partitions AS p  
  ON pg.partition_id = p.partition_id;