need a query to map Object_ID to File_ID

  • Hi, I'm interested in to find out the storage location of a table in MSSQL. I found out, that I can find the File_ID with DBCC ind and find the File Name with e.g. "select physical_name from sys.database_files where file_id in(1,46,47,48)".

    Is anybody able to write a script to catch the return values of DBCC ind for PageFID into a pipe and join this to the sys.database_files table for the physical file name?

  • Tables are specific to a filegroup, not files. If there are multiple files in a filegroup, any table/index created on that filegroup will be spread across all the files in the filegroup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • But if a table is stored in a few pages it can not spread into a lot files, I would like to analyze the table for this pages and find out the file_id and look to the file name. This is manually Sisyphus work. I would like combine the both statements. I need this for critical small tables and file shrink empty to calculate the risk for shrinking specific data files after compression / db reorg.

    drop table #TmpLOGSPACE

    create table #TmpLOGSPACE(

    PageFID varchar(3),

    PagePID varchar(10),

    IAMFID varchar(3),

    IAMPID varchar(10),

    ObjectID varchar(20),

    IndexID varchar(3),

    PartitionNumber varchar(3),

    PartitionID varchar(30),

    iam_chain_type varchar(20),

    PageType varchar(3),

    IndexLevel varchar(3),

    NextPageFID varchar(30),

    NextPagePID varchar(30),

    PrevPageFID varchar(30),

    PrevPagePID varchar(30),

    )

    insert #TmpLOGSPACE(PageFID,PagePID,IAMFID,IAMPID,ObjectID,IndexID,PartitionNumber,PartitionID,iam_chain_type,PageType,IndexLevel,NextPageFID,NextPagePID,PrevPageFID,PrevPagePID)

    EXEC ('DBCC ind("DB","sid.table",-1);')

    select distinct FIL.name from #TmpLOGSPACE AS TMP

    INNER JOIN sys.database_files FIL on FIL.file_id = TMP.PageFID

    faster search

    SELECT distinct OBJECT_NAME(t.object_id) AS ObjectName, d.name AS FileGroup, df.name

    FROM sys.data_spaces d

    JOIN sys.indexes i on i.data_space_id = d.data_space_id

    JOIN sys.tables t on t.object_id = i.object_id

    JOIN sys.database_files df on df.data_space_id = d.data_space_id

    WHERE t.name = 'table'

    order by df.name

  • Risk for shrinking files? What risk?

    Any table over a single page could be spread across multiple files if there are multiple files in the filegroup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ah, ok, so I understand... the parallelization is ever over all files in the file group.

    We had a handle during the shrinking for hours on one of our data files and so a lock situation (deadlock from the application, OS or DB side). Some inserts were waiting for latches or handles and could not update (insert commands). The application SAP has its own lock management and I assume the lock management of the DB or OS and the SAP application works not optimal in this case. SAP is not allowing extra locks additional to its own data model. In the locking situation I was not able to find out the root cause of the handle. The longest running statement was the shrink file empty. As I understand the shrink file locks only the moving pages.

    I would now analyze the tables, which were involved. But if they are all parallelized in the PRIMARY file group, I'm not able to exclude these before. SAP does only support the PRIMARY file group.

    In the online docu it is described the shrinking file should be canceled if a blocking transaction is active. I could not found older statements. The cancel of the shrink file ends after cancel with roll back, but hangs also for hours.

    We could solve the issue only with an restart of the SQL server service.

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

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