I'm looking for a way to efficiently identify the object located closest to the end of a SQL Server data file. This approach needs to remain performant against large data files.
What I have so far
The following query identifies the last object in a given data file (Warning: Don't run this against a database larger than 25 GB unless you want to cancel it at some point):
-- Return object with highest Extent Page ID SELECT files.name as logical_file_name , files.physical_name as physical_file_name , OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS object_name , alloc.* FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, NULL) alloc INNER JOIN sys.database_files files ON alloc.extent_file_id = files.file_id WHERE is_allocated = 1 AND files.name = 'Logical_FileName' ORDER BY files.name , files.physical_name, extent_page_id DESC
What's wrong with this approach
As the Warning above implies, this query will run slower as the size of the database increases because the function is really designed for a pointed approach to look at a specific object and not look at a specific data file in question. When passing in the
NULL parameters as I did, this function likely iterates through all objects within the database behind the scenes and spits out the combined output. This accomplishes what I need, but it does so in a very brute-force way that doesn't lend itself to optimizations.
What I'm asking for
I'm hoping there's a way to iterate through the GAM, SGAM, and/or IAM chains to quickly identify the object at the end of a given data file. I'm assuming I have to push this approach outside of TSQL to something like PowerShell and go back to using DBCC PAGE calls, or something of that nature, traversing page allocation maps to find what the last object for a given data file is. ... and I'm hoping someone has already thrown that code together or knows these structures and/or the output of these undocumented procedures better than I do.
Why do I need this?
This is the inevitable question many will ask, so I'm just going to answer it out of the gate. The project I've been working on is to bring a legacy system up to speed, but after consolidating a bunch of heap tables together (which was a nightmare for other reasons), I am now left with a bunch of free space within my data files. I want to release this space back to the OS, however, the traditional approach of migrating objects to a different data file isn't viable at this stage because I don't have enough free space to work with on the system (until I am able to release more space from this data file).
I've resorted to disabling file growth and running a
DBCC SHRINKFILE TRUNCATEONLY command nightly to free up any open pages toward the end of the data file, but this is a slow and arduous process that may work as often as it doesn't. I'm hoping to identify what the objects toward the end of the file are so I can rebuild them manually and free up space in a quicker timetable.
Is there a way to quickly identify the name of the object located at the end of a given data file? The method I'm employing now is not meeting my needs and I'm open to using any approach available.