Multiple Files & disks

Question

Multiple Files & disks

Rechana Rajan

SSCertifiable

Points: 7721
More actions
February 14, 2017 at 4:42 am

#327280

One of our production DB is very new and growing at a rate of 8GB per month which is in a dedicated server. The server is running on VMware ,we have seperate drive for mdf and ldf file. Right now the DB have only 1 mdf file and is about 40Gb and drive is about 600GB.
Can Gurus help me understand when and at what size do i need to add seperate data file?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Extra files aren't a matter of size. Why do you want to split the data file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

It's early for that now. For one year you'll be about half of the current disk size, so then you'll have to think about the space. My answer is one year later 🙂

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Igor Micev - Tuesday, February 14, 2017 5:10 AM
It's early for that now. For one year you'll be about half of the current disk size, so then you'll have to think about the space. My answer is one year later 🙂

And even then, since it's VMWare, the drive can just be grown, doesn't have to be more files and more disks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 4

GilaMonster - Tuesday, February 14, 2017 5:11 AM
Igor Micev - Tuesday, February 14, 2017 5:10 AM
It's early for that now. For one year you'll be about half of the current disk size, so then you'll have to think about the space. My answer is one year later 🙂
And even then, since it's VMWare, the drive can just be grown, doesn't have to be more files and more disks.

Thanks Gail.

But if the DB grew very large, wont the file\filegroup backup help? keeping multiple files in multiple disk wont help?

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 5

Igor Micev - Tuesday, February 14, 2017 5:10 AM
It's early for that now. For one year you'll be about half of the current disk size, so then you'll have to think about the space. My answer is one year later 🙂

Thanks Igor

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Rechana Rajan - Tuesday, February 14, 2017 5:35 AM
But if the DB grew very large, wont the file\filegroup backup help?

Maybe, depends on a whole pile of things, including recovery requirements, backup destination, recovery model of the DB, skill level of the DBA team and more. Space is one small aspect of that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 7

GilaMonster - Tuesday, February 14, 2017 5:51 AM
Rechana Rajan - Tuesday, February 14, 2017 5:35 AM
But if the DB grew very large, wont the file\filegroup backup help?
Maybe, depends on a whole pile of things, including recovery requirements, backup destination, recovery model of the DB, skill level of the DBA team and more. Space is one small aspect of that.

Thanks Gail.. Can you help me understand how backup destination plays role here ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Rechana Rajan - Tuesday, February 14, 2017 10:59 AM
GilaMonster - Tuesday, February 14, 2017 5:51 AM
Rechana Rajan - Tuesday, February 14, 2017 5:35 AM
But if the DB grew very large, wont the file\filegroup backup help?
Maybe, depends on a whole pile of things, including recovery requirements, backup destination, recovery model of the DB, skill level of the DBA team and more. Space is one small aspect of that.
Thanks Gail.. Can you help me understand how backup destination plays role here ?

If the throughput of the backup destination is low, no amount of optimisation of the read portion of the backup will help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 9

GilaMonster - Tuesday, February 14, 2017 4:13 PM
Rechana Rajan - Tuesday, February 14, 2017 10:59 AM
GilaMonster - Tuesday, February 14, 2017 5:51 AM
Rechana Rajan - Tuesday, February 14, 2017 5:35 AM
But if the DB grew very large, wont the file\filegroup backup help?
Maybe, depends on a whole pile of things, including recovery requirements, backup destination, recovery model of the DB, skill level of the DBA team and more. Space is one small aspect of that.
Thanks Gail.. Can you help me understand how backup destination plays role here ?
If the throughput of the backup destination is low, no amount of optimisation of the read portion of the backup will help.

Thanks Gail. Normally the backups will be taken to locall disk and then moved to some other secure location right and for recovery the same will be copied to local disks again.

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 10

While I agree with Gail that there are a number of factors to consider and skill level of the DBA team is an important consideration, large databases in a single file will create issues down the road with backups and dbcc checkdb runs. It's good that you're thinking about this. By the book, the PRIMARY file is ideally only for the database system tables. Having said by the book, I don't know if this really makes sense or if it's just something someone threw out there. I don't have an in depth understanding of the internals to know. But the reason seems to make sense. If nothing else, I always create a DATA file and make that the default file for all object creation.