January 31, 2013 at 1:31 pm
I got a database which has serveral database files. Some are used and some are not. How can I find out what are being used and what objects are located on it? and what is the best way to move data or indexes from one data file to another?
January 31, 2013 at 1:46 pm
This script from HERE[/url] will tell you good stats on your files
DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command
SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName
Once you've identified what files you want to remove you can empty them like this:
DBCC SHRINKFILE (N'FILENAME' , EMPTYFILE)
Once it's empty you can remove the file using an alter database command
January 31, 2013 at 1:56 pm
Thank you!. The first script is really helpful. but I need to move data from one data file to another before delete the data file. I do not lose any data.
January 31, 2013 at 2:01 pm
As the previous poster stated:
DBCC SHRINKFILE (N'FILENAME' , EMPTYFILE)
That's assuming it's a single filegroup with multiple files
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
January 31, 2013 at 2:02 pm
The second script will move the data in that file to another file in the same filegroup. It will not delete data.
If you have multiple filegroups and you want to move tables from one group to another, then you will want to recreate the clustered index for that table on the new filegroup. For example:
CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [filegroup_name]
January 31, 2013 at 2:04 pm
Sorry I should make this clear at the first place. I have one table that sitting on several file groups. I am trying to consolidate all data into one file group and delete rest of them.
January 31, 2013 at 2:14 pm
How do you have a single table in multiple filegroups?
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
January 31, 2013 at 2:28 pm
Please forgive me. I am new DBA. So here is my situation. When I right click on database name and go to properties, I see several database files and each file in its own filegroup. What I want to do is. first find out what tables or indeses are in those datafiles. then consolidate everything into one data file. Hopefully this make my question clear. Sorry for the confussion.
January 31, 2013 at 2:36 pm
Here is first two colums came out from folloing command. The first column is the object name and second column is filegroup name
SELECT OBJECT_NAME(object_id), *
FROM sys.data_spaces ds
JOIN sys.allocation_units au ON
ds.data_space_id = au.data_space_id
JOIN sys.partitions p ON
au.container_id =
CASE WHEN au.type = 2 THEN p.partition_id
ELSE p.hobt_id END;
GDMNC_FG_GDMN2
GDMNC_FG_GDMN3
GDMNC_FG_GDMN4
GDMNC_FG_GDMN5
GDMNC_FG_GDMN6
GDMNC_FG_GDMN7
GDMNC_FG_GDMN8
GDMNC_FG_GDMN9
January 31, 2013 at 2:47 pm
After dig a little further, I have found there is a partition scheme created and it is pointing to serveral file groups. This table is using this partition scheme.
January 31, 2013 at 3:00 pm
I'd recommend reading a few tips on partitions to get an idea of how they work. Then you can make a decision if you want to remove those partitions.
http://www.mssqltips.com/sql-server-tip-category/65/partitioning/[/url]
Visit that URL - these 2 articles seem relevant to your situation:
- Identify Overloaded SQL Server Partitions (This will help you determine how much each partition is being used)
- Manage multiple partitions in multiple filegroups in SQL Server for cleanup purposes (This will help you understand how partitions are created and modified)
Hope this helps! Good luck!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply