| Author |
Topic |
|
AgaK
Yak Posting Veteran
86 Posts |
 Posted - 2010-10-04 : 14:18:44
|
| HiIs the advice to split the database into multiple data files still accurate in SQL 2008 R2?Thank youAK |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2010-10-04 : 14:33:33
|
| Hi,Thank for you reply. Is it really 64GB? I have a db with a data file of 83GB. My question is more about the accuracy of splitting the db in multiple data files for performance purposes.Thank youAga |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2010-10-04 : 15:09:24
|
| Hi,The db will get bigger. Do you any documentation on this recommendation that you could point me to?Once again thank you.Aga |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-04 : 16:48:58
|
| Tara, can you please provide documentation for your assertion? I see you recommend this all the time, and I have to disagree.The 64GB limitation you are referring to is not related to SQL Server mdf/ldf files. That recommendation is specifically related to NTFS sparse files. The only time this has anything to do with SQL Server is when you are creating database snapshots. And, the only time that becomes an issue is when the actual usage of the sparse files exceeds 64GB (max limitation of sparse files).In other words, if you create a database snapshot of a 100GB database (single file), and leave that snapshot in place until more than 64GB of actual space is used - the sparse file used for the database snapshot could fail. This can cause problems with your system and require the database snapshot to be dropped.You can review this item for further information: http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspxJeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-10-04 : 17:15:01
|
| I don't have any documentation to provide as mentioned in my above post. It was over the phone. And my PSS case was exactly regarding what you've described (mirroring snapshot/sparse files), however the MS engineer said that 64GB is a good cap for data files for performance reasons. We use the 64GB data file max size on all of our systems, and yet we don't use mirroring snapshots anymore.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
multiple data files still accurate?