Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Can .mdf files be split??

Author  Topic 

pattere
Starting Member

3 Posts

Posted - 2010-04-22 : 17:00:56
We are using JDE with SQL Server 2008.

Our consultants set up the database when setting up our new instance of JDE. Several of the .mdf files are 60GB, or more. We would like to split them to span the files over multiple LUNs on our SAN.

I am trying to find out if this is a possibility. I know we can set SQL backups to go to multiple files, but can we split the actual .mdfs themselves, now that they are created?

Thank you,

Eric

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 17:10:24
Yes this is possible and is actually the recommendation. According to Microsoft, it is recommended that no data files be over 64GB in size. We have a system that is currently using 21 data files as the database is large. Those 21 data files are spread across 7 mount points on the SAN which each point to different raid group.

Once you've added the additional files, SQL Server will use a proportional fill algorithm to stripe the data across the files. Since you already have mdf files over 60GB, then you should shrink down the mdf files until they are the same size as the other ones added. You can use the dbcc shrinkfile emptyfile option to help with it.

BTW, the naming convention for secondary files is ndf for the extension. It's just a suggestion of course.

EDIT on 4/9/12: This was bad information from me. I have since learned months ago this was not a general recommendation but was specific to my system. It is NOT recommended to split up your database into multiple files at the 64GB point. It solved an IO performance problem for us due to using different disks, and the 64GB part was due to us also using mirroring and a database snapshot. Because of both of these things, that's why MS recommended this. The wording of the MS PSS engineer is why I believed it to be a general recommendation, but we did have a communication problem, and I misunderstood him.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

pattere
Starting Member

3 Posts

Posted - 2010-04-22 : 17:30:11
Sounds good. Can you point me to any good documentation on accomplishing this? I am, by no means, a DBA. When it comes to SQL, I mostly just do simple queries, and most of those are via a data browser built into JDEdwards. If you can point me to some documentation, I would greatly appreciate it. I do have some others here that can help me. Thank you again.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 17:34:37
I don't have any documentation for this. Data files can be added via the GUI (right click on the database, properties) or via ALTER DATABASE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

pattere
Starting Member

3 Posts

Posted - 2010-04-23 : 08:01:51
I will look into this option. Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-23 : 12:29:59
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

enrightmcc
Starting Member

3 Posts

Posted - 2010-05-21 : 10:35:21
quote:
Originally posted by tkizer

Yes this is possible and is actually the recommendation. According to Microsoft, it is recommended that no data files be over 64GB in size.
Tara Kizer



Tara, can you point me to a site that says that Microsoft recommends that no data files be over 64GB in size?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 11:18:04
I don't have a link as I heard it directly from Microsoft during a PSS case that I opened. The limitation has to do with Windows sparse files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SQLSoldier
Master of MCMs

9 Posts

Posted - 2012-04-05 : 10:43:22
No such recommendation exists. This is very bad advice, in my opinion. In fact, many people (including Paul Randal and SQLCAT) have disproven the claim that using multiple data files increases performance. The only way having more data files increases performance is if you are spreading them across multiple dedicated disk drives/LUNs.

There is NO benefit from doing this, but it increased management overhead.

The only thing I can think that you are referring to is that Windows 2008 RTM had a bug that would cause database snapshots to fail if the file was large. the correct fix for that is to install the patch for the OS (or upgrade to a current version now).

Robert L Davis
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and chief SQL Evangelist
Idera
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-05 : 11:08:39
There is a recommendation on max size from the Sharepoint team for Sharepoint (and only sharepoint) databases. I know they have a max size that they say mdf files should be, but it is solely for sharepoint, not a general SQL recommendation,

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-05 : 11:10:01
p.s. 2 year old thread

--
Gail Shaw
SQL Server MVP
Go to Top of Page

enrightmcc
Starting Member

3 Posts

Posted - 2012-04-05 : 11:37:55
quote:
Originally posted by GilaMonster

p.s. 2 year old thread
--
Gail Shaw
SQL Server MVP



Yes it is, Gail. But in this case it's good information that is still applicable. tkizer was presenting the worst type of information in that he "heard it directly from Microsoft during a PSS case that [he] opened." Furthermore his signature says he's an MVP. To some people he has the credentials to be trusted; and he's giving information that has yet to be verified. Personally I'm glad Robert Davis (SQLSoldier), and you came along and added comments to an old thread.
-Bob McC
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-05 : 13:13:18
p.p.s. Tara is female.

MVPs can be wrong too, especially when we're told something by someone at Microsoft and assume that they are correct.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 13:25:02
How does Robert get a Custome Title with 1 post? His winning personality?

And who is Bob?

Did you perhaps not assume that Tara DID mean across multiple Disks...better to tread lightly

Little knowledge is a dangerous thing



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-05 : 13:48:06
Makes you want to run right out and do business with Idera huh?
Go to Top of Page

SQLSoldier
Master of MCMs

9 Posts

Posted - 2012-04-05 : 14:59:14
I don't have to ASSUME that Tara is or is not meaning dedicated drives as she clearly stated that they have 21 files spread across 7 mount points. CLEARLY that's not separate dedicated drives.

I don't know how I got a custom title, but I can tell you where it came from. Prior to my current position, I was the program manager for the SQL Server Certified Master program at Microsoft Learning.

To add on to what Gail said, everybody can be wrong. MVPs, MCMs, PSS engineers.

Robert L Davis
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Evangelist
Idera
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2012-04-05 : 16:34:54
MR davis as i would trust tara's word over anyone else on this site.

Try this one

Select id = IDENTITY(INT,1,1),'A' as Alpa
into #decleration

DECLARE @textXML1 XML DECLARE @data1 NVARCHAR(MAX),@delimiter1 NVARCHAR(5)

SELECT @data1 = 'B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z',
@delimiter1 = ',' SELECT @textXML1 = CAST('<d>' + REPLACE(@data1, @delimiter1, '</d><d>') + '</d>' AS XML)
INSERT INTO dbo.#decleration SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML1.nodes('/d') T(split)

Create table #note (num int)
ALTER TABLE #note add NID int identity

DECLARE @textXML2 XML DECLARE @data2 NVARCHAR(MAX),@delimiter2 NVARCHAR(5)
SELECT @data2 = '25,15,21,33,2,15,14,5,35,8,5,1,4',
@delimiter2 = ',' SELECT @textXML2 = CAST('<d>' + REPLACE(@data2, @delimiter2, '</d><d>') + '</d>' AS XML)
INSERT INTO dbo.#note SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML2.nodes('/d') T(split)


Select Alpa
from dbo.#decleration right outer join dbo.#note on dbo.#decleration.id = dbo.#note.num
order by NID
drop table dbo.#decleration
drop table dbo.#note

Jim
Users <> Logic
Go to Top of Page

SQLSoldier
Master of MCMs

9 Posts

Posted - 2012-04-05 : 16:56:13
I'm not saying you shouldn't trust advice from Tara. All I'm sating is that this particular advice is wrong.

Robert L Davis
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Evangelist
Idera
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-05 : 17:10:20

Oh..play nice now


Former Microsoft Certified Master: SQL Server 2008 (Fired)
Sr. Product Consultant (Salesman)
Chief SQL Evangelist (blowhard)



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

SQLSoldier
Master of MCMs

9 Posts

Posted - 2012-04-05 : 17:16:32
I'm not a FORMER Certified Master. I am a Certified Master. Nor am I a sales/marketing person. I am a mature adult which seems to be in short supply around here.

Robert L Davis
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Evangelist
Idera
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-05 : 17:17:14
quote:
Originally posted by X002548


Oh..play nice now

[ white ]
Former Microsoft Certified Master: SQL Server 2008 (Fired)
Sr. Product Consultant (Salesman)
Chief SQL Evangelist (blowhard)

[ /white ]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/



I'm frankly embarrassed to be part of this site now...

--
Gail Shaw
SQL Server MVP
Go to Top of Page
    Next Page

- Advertisement -