September 23, 2014 at 7:07 am
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?
September 23, 2014 at 7:09 am
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
September 23, 2014 at 7:13 am
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
September 23, 2014 at 7:25 am
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
September 23, 2014 at 7:42 am
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