Determining objects in secondary datafile

Question

Determining objects in secondary datafile

Marti Ryan

Ten Centuries

Points: 1058
More actions
November 28, 2005 at 10:58 am

#112818

Hi all - I hope this is a good place to post this question. I have taken over administering a database (125GB - 2700+ tables, 2800+ sprocs, 25+ UDF's and a handful of views)...not to mention I am a newbie at all of this.
It is a "home-grown" db that has not been 'cared for' in the past, and documentation is non-exisitant. There are currently 2 datafiles, both located on PRIMARY filegroup, and of course one *.ldf file. All 3 files exist on the same harddrive (I have been fighting this one as well).
My question is simple: is there a way to determine what objects exist on the *.ndf file vs. the *.mdf file, since both reside on the PRIMARY filegroup?
Thanks for not laughing at my question.
-Marti

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

No - most objects will have data stored in both files - SQL Server (loosely) round-robins allocations from all files in a filegroup (I can go into details of how this works if you're interested).

Regards

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Marti Ryan Ten Centuries Points: 1058 More actions · Answer 2

Thanks Paul - I was suspicious of that. And yes - I would like details, as I am most certainly going to be asked for them. Even if you can point me in the right direction, research wise, that would be helpful!

-Marti

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

This is the mechanism that the allocation code uses to determine which file to allocate from next when a filegroup contains multiple files. It ensures that allocations are fairly distributed between the files. Each file has a weighting, with at least one file having a weighting of 1, which ensures an allocation can always be made on the first pass through the weighting list. The weightings are recalculated after a certain number of allocations or when a file size changes or the file list changes. The pseudo-code that uses the weightings looks something like this:

while (1)

{

currentFile = ++currentFile MOD numberOfFiles;

if (currentWeighting [currentFile] != 1)

{

currentWeighting [currentFile]--;

}

else

{

// Reset the weighting again.

//

currentWeighting [currentFile] = storedWeighting [currentFile];

break;

}

Hope that helps.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Question Paul, does the alogorithm rotate from one file to the next within the filegroup based on extent allocation or allocation unit allocation ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Extents

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005