See what data is in what SQL Server data file?

Question

At one point my data drive on my SQL Server server was about to run out of space so I had to add a second file to the PRIMARY data group and put a limit on the growth of the first file. That caused new data to go into the second file, which is good. Now I'd like to know which tables have data in the first file so that I can know which tables will be locked when I migrate that data over to the second file. Is there a way to view this?

Answer 1

You can use this script to see which tables are on which file group and their actual physical location

SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid 
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Answer 2

Enclosed is a sample database that resembles your situation.

-- Create database
CREATE DATABASE [out_of_space] 
 ON  PRIMARY 
( NAME = N'out_of_space_dat', FILENAME = N'C:\mssql\data\out_of_space_dat.mdf' , SIZE = 4MB , FILEGROWTH = 1MB, MAXSIZE = 4MB )
 LOG ON 
( NAME = N'out_of_space_log', FILENAME = N'C:\mssql\log\out_of_space_log.ldf' , SIZE = 1MB , FILEGROWTH = 4MB , MAXSIZE = 4MB )
GO

-- Switch to correct database
Use [out_of_space];
GO

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Temp')
DROP SCHEMA [Temp]
GO

-- Add new schema.
CREATE SCHEMA [Temp] AUTHORIZATION [dbo]
GO

-- Delete existing table
IF  OBJECT_ID(N'[Temp].[PageSplits]') > 0
    DROP TABLE [Temp].[PageSplits]
GO

-- Create new table
CREATE TABLE [Temp].[PageSplits]
(
    [SplitId] [int] IDENTITY (1, 1) NOT NULL,
    [SplitGuid] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWSEQUENTIALID()),
    [SplitDt] [datetime] NOT NULL DEFAULT (getdate()),
    CONSTRAINT [pk_Split_Guid] PRIMARY KEY CLUSTERED 
    ( [SplitGuid] ASC )
) 
GO

-- Make 50K of records
DECLARE @VAR_CNT INT = 1;
WHILE (@VAR_CNT <= 50000)
BEGIN
    INSERT [Temp].[PageSplits] DEFAULT VALUES;
    SET @VAR_CNT = @VAR_CNT + 1;
END
GO

-- Get record count
SELECT COUNT(*) AS TOTAL_RECS 
FROM [Temp].[PageSplits]
GO


-- Error Message

/*

Msg 1105, Level 17, State 2, Line 5
Could not allocate space for object 'Temp.PageSplits'.'pk_Split_Guid' in database 'out_of_space' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

*/

-- Show me the data file
sp_spaceused '[Temp].[PageSplits]'

/*

name        rows    reserved    data    index_size  unused
PageSplits  46870   1736 KB     1720 KB 16 KB       0 KB

*/

Basically, I made the first primary data file run out of space.

When you added an additional data file, it automatically was added to the primary file group.

-- Add another file
ALTER DATABASE [out_of_space] 
ADD FILE 
(
    NAME = out_of_space_dat2,
    FILENAME =  N'C:\mssql\data\out_of_space_dat2.ndf',
    SIZE = 2MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 2MB
);
GO


-- Make 5K of records
DECLARE @VAR_CNT INT = 1;
WHILE (@VAR_CNT <= 5000)
BEGIN
    INSERT [Temp].[PageSplits] DEFAULT VALUES;
    SET @VAR_CNT = @VAR_CNT + 1;
END
GO

There are many ways to do things like find space usage in SQL Server.

First is the catalog views. The solution by Ali does not show pages used, max size etc.

-- Get allocation units by file and partition
select 
    OBJECT_NAME(p.object_id) as my_table_name, 
    u.type_desc,
    f.file_id,
    f.name,
    f.physical_name,
    f.size,
    f.max_size,
    f.growth,
    u.total_pages,
    u.used_pages,
    u.data_pages,
    p.partition_id,
    p.rows
from sys.allocation_units u 
    join sys.database_files f on u.data_space_id = f.data_space_id 
    join sys.partitions p on u.container_id = p.hobt_id
where 
    u.type in (1, 3)  and 
    OBJECT_NAME(p.object_id) = 'PageSplits'
GO

My solution does give you that information.

Another way to get this information is from the Dynamic management views.

-- Management view (partitions)
SELECT * FROM sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('Temp.PageSplits');
GO

-- Management view (files)
SELECT db_name(database_id) as database_nm, * FROM sys.dm_db_file_space_usage
GO

The only way to rebuild the table so that it is on one file is to create a new file group and a new file. Make sure the file is linked to the new group.

-- Add a new file group
ALTER DATABASE [out_of_space]
ADD FILEGROUP fg_new_space
GO

-- Add the third data file
ALTER DATABASE [out_of_space] 
ADD FILE 
(
    NAME = out_of_space_dat3,
    FILENAME =  N'C:\mssql\data\out_of_space_dat3.ndf',
    SIZE = 2MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 2MB
)
TO FILEGROUP fg_new_space
GO

Drop the constraint and move the data to the new file group. Build a new primary key which defaults to the table file group. Just what we want.

-- Drop the constraint
ALTER TABLE [Temp].[PageSplits] DROP CONSTRAINT [pk_Split_Guid] WITH (MOVE TO [fg_new_space])
GO

-- Add back the primary key
ALTER TABLE [Temp].[PageSplits] ADD
CONSTRAINT [pk_Split_Guid] PRIMARY KEY CLUSTERED 
    ( [SplitGuid] ASC );

One last item to make this article complete. We can get space counts via SSMS. Lets look at the table properties.

Lets look at the index properties.

Both the table and the cluster index are now on the new file/file group.