May 4, 2015 at 11:41 pm
Hi,
I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.
I was trying this:
select f.name, *
from sysfiles f (nolock)
left join sys.filegroups fg (nolock)
on f.name = fg.name
left join sys.indexes i (nolock)
on i.data_space_id = fg.data_space_id
left join sys.all_objects o (nolock)
ON i.[object_id] = o.[object_id]
where i.name is NULL and o.name is NULL
Did not work.
Any ideas?
Thanks.
P.S.: My file names are the same as the filgroup names containing the file, this is why I was joining by name.
May 5, 2015 at 3:30 am
Give some more information about your tables..
May 5, 2015 at 4:18 am
7 tables partitioned by month, 41 partitions, each month is in its own file and filegroup. The name of the file and the filegroup it belongs to is the same.
The first and the last partitions are always empty. Ideally I want to exclude them and only have the files that became empty as a result of partition switching and archival.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply