August 28, 2009 at 8:17 am
Hi guys. I've 4 files in the primary filegroup. 3 of the files are 1TB files which have about 50% free space.
My goal is to move all non-system tables out of primary FG. But I need space on that drive to be released.
What I need to find out is this:
TableA resides on which file(s)? I understand we can find the FG and stuff but is it possible to find which file holds all or majority of this table? I would like to mvoe all tables on one file first, remove the file and then move onto the next file.
Also, what's the fastest\best way to get rid of a 1TB file which has 500GB free(it won't grow anymore). shrinkfile or emptyfile or something else?
Thank you for your help.
August 28, 2009 at 8:47 am
sys.indexes has the data_space_id, which is the ID for the filegroup. The clustered index is where the table data is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2009 at 9:22 am
I understand that will give you the FG info. I want the file info...
I want to know which file does the table reside on(completely or mostly or evenly). For e.g.
TableA resides on data_space_id=2, file=1...
September 2, 2009 at 6:48 am
Haven't ever needed to know that. Not sure where to get it. Dig around a bit in the system views. It's probably in there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 7:05 am
not sure if it helps but i think that if you create a clustered index or recreate it on a different Filegroup. the data is moved as well
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 7:56 am
Silverfox (9/2/2009)
not sure if it helps but i think that if you create a clustered index or recreate it on a different Filegroup. the data is moved as well
You're correct that moving the clustered index effectively moves the table, but that's just which filegroup, not which particular file in the group.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 8:01 am
ok fair enough, Now I have to look into it, purely for my own curiosity now
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 8:24 am
I'm not 100% sure if this will answer your question or not, but I recently ran into a similar issue where I had 40+ filegroups and had to locate which table/indexes/etc were on a filegroup.
Here were a couple of things that I used:
1) This is a pretty robust script that can locate data stored on filegroups:
http://www.sqlmag.com/Article/ArticleID/101051/sql_server_101051.html
2) Another one, not as robust:
http://www.cryer.co.uk/brian/sqlserver/howtolisttorfifilegroup.htm
3) A third:
http://sqlblog.com/blogs/eric_johnson/default.aspx
Sorry if I'm off track here.
-Ken
September 2, 2009 at 8:45 am
off track I am afraid.
1st one does it for partitioned tables
and the others dont answer it either.
the op wants to know which file in a multiple filegroup scenario contains which table.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 9:02 am
I cannot find it lol.
been looking through the system tables/views. need someone with more knowledge of sql internals. It should be possible to tie it down using the data pages. maybe you can tie in, dbcc page results with sysaltfiles somehow
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 10:03 am
Silverfox (9/2/2009)
off track I am afraid.1st one does it for partitioned tables
and the others dont answer it either.
the op wants to know which file in a multiple filegroup scenario contains which table.
A table could be spread across multiple files in the filegroup.
September 2, 2009 at 4:16 pm
I was curious about the same thing recently. What I found was that when you have multiple files in a filegroup, you end up with the table splitting across the files in that filegroup.
There are scripts that can help to show this. As you move a clustered index to a new filegroup, the data will move (most data, not all data). If you watch the growth of the files in the filegroup, you will notice that they grow semi-synchronously as you move each table. Do this one table at a time and you will see what I mean.
I have also tried to find exactly how to determine how much of each table is in each file (if more than one file), and was unable to come up with that info. Not all tables in my experience split evenly across the files in the filegroup - thus the inquiry into how much per file in the filegroup.
This is a script I like to use to monitor filegroup growth as data is pumped into or out of it:
CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
"Name" nvarchar( 128 ) , "FileName" nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
--Data File Details
INSERT INTO #FileDetails (
FileId , FileGroupId , TotalExtents , UsedExtents , "Name" , "Filename"
)
EXECUTE( 'dbcc showfilestats with tableresults' )
SELECT FILEGROUP_NAME( FileGroupID ) AS FileGroupName ,
FileId ,
"Name" ,
"FileName" ,
(CONVERT(decimal(38,2),TotalSize)) AS FileSizeMB ,
(CONVERT(decimal(38,2),UsedSize)) AS CurrentSizeMB ,
(CONVERT(decimal(38,2),((UsedExtents*1.)/TotalExtents)*100)) AS "%Usage"
FROM #FileDetails
Select sum(CONVERT(decimal(38,2),UsedSize)) as CurrentDBSize,sum(CONVERT(decimal(38,2),TotalSize)) as CurrentDBAllocatedSize from #FileDetails
DROP TABLE #FileDetails
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 2, 2009 at 4:57 pm
Tables and indexes are assigned to filegroups, not files, so the normal assumption will be that the data is spread across all files in the filegroup.
If you run DBCC SHRINKFILE with the EMPTYFILE option, it will move all the data out of the file to the other files in the filegroup.
September 3, 2009 at 12:42 am
In SQL Server, FileGroup provides data placement control but if you've created multiple files in a Filegroup SQL will use split and write technique to write simultaneously on all the files in a Filegroup and the algo. to split the data also depends upon number of things.
If you want to move single table or few tables on a single file than create a filegroup and add only a single file to it and then move\create table on that filegroup.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
September 3, 2009 at 2:15 am
Like it has been mentioned you can always take the biggest tables and put them in a separate filegroup. finding out the data pages allocation by table by file, seems to be more trouble than it is worth.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply