See what data is in the SQL Server data file?

Question

At some point, my data disk on my SQL Server was exhausted, so I had to add a second file to the PRIMARY data group and set a limit on the growth of the first file. This led to the fact that new data was included in the second file, which is good. Now I would like to know which tables have data in the first file so that I can know which tables will be locked when transferring this data to the second file. Is there any way to view this?

Answer 1

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 exhausted.

When you added an additional data file, it is automatically added to the main 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 use overlay space in SQL Server.

First, these are catalog views. Ali's solution does not show pages used, maximum 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 gives you this information.

Another way to get this information is from dynamic control 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 is to create a new group of files and a new file in one file. Verify that the file is associated with 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 

Remove the restriction and move the data to a new group of files. Create a new primary key, which by default belongs to the group of table files. 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 element to complete this article. We can count the number of spaces through SSMS. Let's look at the table properties.

Let's look at the properties of the index.

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

Answer 2

You can use this script to see which tables are in a group of files 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