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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
pattere
Starting Member
3 Posts |
Posted - 2010-04-23 : 08:01:51
|
I will look into this option. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 DavisMicrosoft Certified Master: SQL Server 2008Sr. Product Consultant and chief SQL EvangelistIdera |
|
|
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 ShawSQL Server MVP |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-04-05 : 11:10:01
|
p.s. 2 year old thread--Gail ShawSQL Server MVP |
|
|
enrightmcc
Starting Member
3 Posts |
Posted - 2012-04-05 : 11:37:55
|
quote: Originally posted by GilaMonster p.s. 2 year old thread--Gail ShawSQL 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 |
|
|
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 ShawSQL Server MVP |
|
|
X002548
Not Just a Number
15586 Posts |
|
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? |
|
|
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 DavisMicrosoft Certified Master: SQL Server 2008Sr. Product Consultant and Chief SQL EvangelistIdera |
|
|
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 oneSelect id = IDENTITY(INT,1,1),'A' as Alpainto #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 identityDECLARE @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 Alpafrom dbo.#decleration right outer join dbo.#note on dbo.#decleration.id = dbo.#note.numorder by NIDdrop table dbo.#decleration drop table dbo.#noteJimUsers <> Logic |
|
|
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 DavisMicrosoft Certified Master: SQL Server 2008Sr. Product Consultant and Chief SQL EvangelistIdera |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 DavisMicrosoft Certified Master: SQL Server 2008Sr. Product Consultant and Chief SQL EvangelistIdera |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
Next Page
|