Equal Data file size question.

Question

Equal Data file size question.

Tac11

SSCertifiable

Points: 6976
More actions
December 7, 2014 at 7:56 am

#312957

I have 3 data files on my database.
1. .mdf is 300 gb.
2. .ndf is 250 gb
3. .ndf is 75 gb
all have less than 10% space available. I read some where it is best practice to make all file sizes same. My question is how do I make above size to same or almost same without making database bigger size?
thank you

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

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Tac11 (12/7/2014)
I have 3 data files on my database.
1. .mdf is 300 gb.
2. .ndf is 250 gb
3. .ndf is 75 gb
all have less than 10% space available. I read some where it is best practice to make all file sizes same. My question is how do I make above size to same or almost same without making database bigger size?
thank you

Quick questions; do you have one (default PRIMARY) filegroup? Any reason or problems you are trying to solve with this? What type of data/primary activity is in the database (transactional, datamart, data warehouse)? How much storage space do you have (needed for making changes, not permanently)? What is the growth rate (gb/month)? What is the data/index ratio (data/clustered index vs. non-clustered index space)?

😎

Tac11 SSCertifiable Points: 6976 More actions · Answer 2

Thanks for your reply. I have followings for your questions. Hope this helps.

1. Yes, 1 primary file group

2. I thought it is best practice to keep all data files same size (may be it theory only for tempdb data files)

3. OLTP environment (insurance company)

4. space available is limited

5. Growth rate 10 GB monthly.

6. Not sure can, you plz explain in details?

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

Tac11 (12/7/2014)
....
6. Not sure can, you plz explain in details?

Good stuff, now we are getting somewhere;-)

The data/index ratio is the ratio between the raw data and the duplication of data residing in indexes (on index pages). Look into sys.partitions and sys.allocation_units, for short you can post the output of this query for the relevant table

😎

SELECT

OBJECT_NAME(P.object_id)

,*

FROM sys.partitions P

INNER JOIN sys.allocation_units A

ON P.hobt_id = A.container_id;

Tac11 SSCertifiable Points: 6976 More actions · Answer 4

I am trying to give some info so it might help I think. I am concern about first 2 rows (came out same table name) which is the biggest table in the database.

Outputs: Order by total_pages desc

Object_ID are same (First 2 rows are same table name)

Partition Id are same on both rows

Object ID are same

Index Id are same (1)

Hobt_Id are same

Rows are same (29887396)

Allocation_Unit_ID different

Type are different (2 and 1)

Type_desc different (LOB_DATA and IN_ROW_DATA)

Container_ID are same

Dat_space_id are same (1)

Total_pages are different (101278924 and 9337492)

Used_pages are different (101264223, 9272979)

Data_pages (0 , 9211823)

Thanks for you help.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

getting your mates to ask the same question are you??

http://www.sqlservercentral.com/Forums/Topic1642361-391-1.aspx

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Tac11 SSCertifiable Points: 6976 More actions · Answer 6

Tac11

SSCertifiable

Points: 6976

December 9, 2014 at 6:34 am

#1763497

No sir, I swear. I also surprised!!!