Number of database files

I am looking after an SAP database on SQL 2000 and its current size is
approx 190Gb. Due to the original people who installed the system years ago
the database is stored in one file. My question is, will this cause me
problems as the database size increases? I know it will be fine size wise
I'm thinking more of performance issues. The file is on a S.A.N so disk
speed wise it is pretty good.
thanks
GavGav
In my experience with SQL 2000, I have not come accross a database that
needed more than one data file. For large databases they tend to be spread
accross a number of physical disks, so I/O is not usually an issue. (Given
modern disk capability). In version 7 and before you got a benifit when
performing backups and accessing large tables where the database was split
over multiple data files. (Though you should still have no more data files
than physical disks).
You might want to use multiple data files in conjunction with multiple file
groups to control physical placement of your tables. Again though in practice
SQL 2000 does a good job of striping the data making this rarely needed.
I used to work for a manager who kept database devices (back in the 6.5
days) limited to 2gb as you could edit files up to 2gb. You could do this
with data files, if you saw any need to edit the files, but at 190gb it's a
bit too big to be useful.
If you are not experiencing performance problems that seem to relate to the
data file I would not worry about it.
Hope this helps
John
"Gav" wrote:
> I am looking after an SAP database on SQL 2000 and its current size is
> approx 190Gb. Due to the original people who installed the system years ago
> the database is stored in one file. My question is, will this cause me
> problems as the database size increases? I know it will be fine size wise
> I'm thinking more of performance issues. The file is on a S.A.N so disk
> speed wise it is pretty good.
> thanks
> Gav
>
>|||Having one big file has a bit of risk attached to it, and
is slightly slower than an alternative.
Firstly if that one file goes down then you will need to
restore the whole lot, which can take a bit of a time to
do, for safety its better to split the file onto different
disk drives, that way if one goes you still have your data
safely on another disk (or if you use raiding different
set of disks).
The second is performance. If you put some files on disk
drive C, and disk drive D you will have two different
disks read write heads working for you that will improve
your performance.
You could increase performance even more by proper raiding
and splitting the files. For instance if you have a group
of tables that are constently been written to you can set
the raid to be say 10 for those tables, but on a different
set of disks configure it to be raid 5.
One point though is that usually having one file will do
however if you can get away with it, have more.
Peter
"The best minds are not in government. If any were,
business would steal them away."
Ronald Reagan
>--Original Message--
>I am looking after an SAP database on SQL 2000 and its
current size is
>approx 190Gb. Due to the original people who installed
the system years ago
>the database is stored in one file. My question is, will
this cause me
>problems as the database size increases? I know it will
be fine size wise
>I'm thinking more of performance issues. The file is on a
S.A.N so disk
>speed wise it is pretty good.
>thanks
>Gav
>
>.
>|||"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:aa3601c4d6c9$62a38270$a601280a@.phx.gbl...
> Having one big file has a bit of risk attached to it, and
> is slightly slower than an alternative.
> Firstly if that one file goes down then you will need to
> restore the whole lot, which can take a bit of a time to
> do, for safety its better to split the file onto different
> disk drives, that way if one goes you still have your data
> safely on another disk (or if you use raiding different
> set of disks).
>
Won't you have to restore everything anyway? How do you
keep data consistancy between the 2 or more files? If you only
restore one file surely the restored file will be out of date compared to
the still existing file or files. Unless I'm missing something.
> The second is performance. If you put some files on disk
> drive C, and disk drive D you will have two different
> disks read write heads working for you that will improve
> your performance.
> You could increase performance even more by proper raiding
> and splitting the files. For instance if you have a group
> of tables that are constently been written to you can set
> the raid to be say 10 for those tables, but on a different
> set of disks configure it to be raid 5.
At the moment the entire database is raid 10. If I was to split
the database into more than one file how would I go about it?
It contains approx 38,000 tables, would I have to assign half to
a different file group?
> One point though is that usually having one file will do
> however if you can get away with it, have more.
> Peter
> "The best minds are not in government. If any were,
> business would steal them away."
> Ronald Reagan
>
>
> >--Original Message--
> >I am looking after an SAP database on SQL 2000 and its
> current size is
> >approx 190Gb. Due to the original people who installed
> the system years ago
> >the database is stored in one file. My question is, will
> this cause me
> >problems as the database size increases? I know it will
> be fine size wise
> >I'm thinking more of performance issues. The file is on a
> S.A.N so disk
> >speed wise it is pretty good.
> >
> >thanks
> >
> >Gav
> >
> >
> >.
> >|||>Won't you have to restore everything anyway? How do you
>keep data consistancy between the 2 or more files? If you
only
>restore one file surely the restored file will be out of
date compared to
>the still existing file or files. Unless I'm missing
something.
No, you can back up filegroup, so as long as the filegroup
is on a different disk all you need to restore is that
filegroup.
Its more of a shortcut, you will need downtime of course,
but restore saying 90gb is going to take less time than
190gb.
>At the moment the entire database is raid 10. If I was to
split
>the database into more than one file how would I go about
it?
>It contains approx 38,000 tables, would I have to assign
half to
>a different file group?
This will not just be a software change but a hardware
change as well. You will need to create drives i.e C: D:
E: F ect.
What you would do then is create different groups on
different disks. Unfortunatly its not as easy as giving
them a different file group as you will need to load the
data into the tables with the new filegroups, you can't
just change the table though an alter table command.
I'm going to stick my neck out here and say if you
don't 'need' to then don't. You are already on the best
kind of raid (if you can afford it) otherwise its going to
be a shed load of work to get it all working.
Peter
"They say that you only learn by mistake, does that mean I
am the worlds most learned man?"
Peter The Spate
>--Original Message--
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:aa3601c4d6c9$62a38270$a601280a@.phx.gbl...
>> Having one big file has a bit of risk attached to it,
and
>> is slightly slower than an alternative.
>> Firstly if that one file goes down then you will need to
>> restore the whole lot, which can take a bit of a time to
>> do, for safety its better to split the file onto
different
>> disk drives, that way if one goes you still have your
data
>> safely on another disk (or if you use raiding different
>> set of disks).
>Won't you have to restore everything anyway? How do you
>keep data consistancy between the 2 or more files? If you
only
>restore one file surely the restored file will be out of
date compared to
>the still existing file or files. Unless I'm missing
something.
>> The second is performance. If you put some files on disk
>> drive C, and disk drive D you will have two different
>> disks read write heads working for you that will improve
>> your performance.
>> You could increase performance even more by proper
raiding
>> and splitting the files. For instance if you have a
group
>> of tables that are constently been written to you can
set
>> the raid to be say 10 for those tables, but on a
different
>> set of disks configure it to be raid 5.
>At the moment the entire database is raid 10. If I was to
split
>the database into more than one file how would I go about
it?
>It contains approx 38,000 tables, would I have to assign
half to
>a different file group?
>> One point though is that usually having one file will do
>> however if you can get away with it, have more.
>> Peter
>> "The best minds are not in government. If any were,
>> business would steal them away."
>> Ronald Reagan
>>
>>
>> >--Original Message--
>> >I am looking after an SAP database on SQL 2000 and its
>> current size is
>> >approx 190Gb. Due to the original people who installed
>> the system years ago
>> >the database is stored in one file. My question is,
will
>> this cause me
>> >problems as the database size increases? I know it will
>> be fine size wise
>> >I'm thinking more of performance issues. The file is
on a
>> S.A.N so disk
>> >speed wise it is pretty good.
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>
>.
>|||Oke doke. I was not planning on doing it, just wondering out of interest. I
have a test restore box with the full database on it so I can play about
with these sort of things.
Gav :o)
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:aaad01c4d6d8$90280b80$a601280a@.phx.gbl...
> >Won't you have to restore everything anyway? How do you
> >keep data consistancy between the 2 or more files? If you
> only
> >restore one file surely the restored file will be out of
> date compared to
> >the still existing file or files. Unless I'm missing
> something.
> No, you can back up filegroup, so as long as the filegroup
> is on a different disk all you need to restore is that
> filegroup.
> Its more of a shortcut, you will need downtime of course,
> but restore saying 90gb is going to take less time than
> 190gb.
> >At the moment the entire database is raid 10. If I was to
> split
> >the database into more than one file how would I go about
> it?
> >It contains approx 38,000 tables, would I have to assign
> half to
> >a different file group?
> This will not just be a software change but a hardware
> change as well. You will need to create drives i.e C: D:
> E: F ect.
> What you would do then is create different groups on
> different disks. Unfortunatly its not as easy as giving
> them a different file group as you will need to load the
> data into the tables with the new filegroups, you can't
> just change the table though an alter table command.
> I'm going to stick my neck out here and say if you
> don't 'need' to then don't. You are already on the best
> kind of raid (if you can afford it) otherwise its going to
> be a shed load of work to get it all working.
> Peter
> "They say that you only learn by mistake, does that mean I
> am the worlds most learned man?"
> Peter The Spate
>
>
>
> >--Original Message--
> >
> >"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:aa3601c4d6c9$62a38270$a601280a@.phx.gbl...
> >> Having one big file has a bit of risk attached to it,
> and
> >> is slightly slower than an alternative.
> >>
> >> Firstly if that one file goes down then you will need to
> >> restore the whole lot, which can take a bit of a time to
> >> do, for safety its better to split the file onto
> different
> >> disk drives, that way if one goes you still have your
> data
> >> safely on another disk (or if you use raiding different
> >> set of disks).
> >>
> >
> >Won't you have to restore everything anyway? How do you
> >keep data consistancy between the 2 or more files? If you
> only
> >restore one file surely the restored file will be out of
> date compared to
> >the still existing file or files. Unless I'm missing
> something.
> >
> >> The second is performance. If you put some files on disk
> >> drive C, and disk drive D you will have two different
> >> disks read write heads working for you that will improve
> >> your performance.
> >>
> >> You could increase performance even more by proper
> raiding
> >> and splitting the files. For instance if you have a
> group
> >> of tables that are constently been written to you can
> set
> >> the raid to be say 10 for those tables, but on a
> different
> >> set of disks configure it to be raid 5.
> >
> >At the moment the entire database is raid 10. If I was to
> split
> >the database into more than one file how would I go about
> it?
> >It contains approx 38,000 tables, would I have to assign
> half to
> >a different file group?
> >
> >> One point though is that usually having one file will do
> >> however if you can get away with it, have more.
> >>
> >> Peter
> >>
> >> "The best minds are not in government. If any were,
> >> business would steal them away."
> >> Ronald Reagan
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I am looking after an SAP database on SQL 2000 and its
> >> current size is
> >> >approx 190Gb. Due to the original people who installed
> >> the system years ago
> >> >the database is stored in one file. My question is,
> will
> >> this cause me
> >> >problems as the database size increases? I know it will
> >> be fine size wise
> >> >I'm thinking more of performance issues. The file is
> on a
> >> S.A.N so disk
> >> >speed wise it is pretty good.
> >> >
> >> >thanks
> >> >
> >> >Gav
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I just wanted to clarify one comment that Peter made.
>> No, you can back up filegroup, so as long as the filegroup
>> is on a different disk all you need to restore is that filegroup.
While it is true you can do file or filegroup backups and restores there is
a little more to it than just restoring the backup for that file or
filegroup. You must then restore all the log files from the point in which
you backed up the file or filegroup you are restoring. This is what keeps
all of the database in sync with the newly restored backup.
--
Andrew J. Kelly SQL MVP
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cohue6$lt2$1@.sparta.btinternet.com...
> Oke doke. I was not planning on doing it, just wondering out of interest.
> I
> have a test restore box with the full database on it so I can play about
> with these sort of things.
> Gav :o)
> "Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
> news:aaad01c4d6d8$90280b80$a601280a@.phx.gbl...
>> >Won't you have to restore everything anyway? How do you
>> >keep data consistancy between the 2 or more files? If you
>> only
>> >restore one file surely the restored file will be out of
>> date compared to
>> >the still existing file or files. Unless I'm missing
>> something.
>> No, you can back up filegroup, so as long as the filegroup
>> is on a different disk all you need to restore is that
>> filegroup.
>> Its more of a shortcut, you will need downtime of course,
>> but restore saying 90gb is going to take less time than
>> 190gb.
>> >At the moment the entire database is raid 10. If I was to
>> split
>> >the database into more than one file how would I go about
>> it?
>> >It contains approx 38,000 tables, would I have to assign
>> half to
>> >a different file group?
>> This will not just be a software change but a hardware
>> change as well. You will need to create drives i.e C: D:
>> E: F ect.
>> What you would do then is create different groups on
>> different disks. Unfortunatly its not as easy as giving
>> them a different file group as you will need to load the
>> data into the tables with the new filegroups, you can't
>> just change the table though an alter table command.
>> I'm going to stick my neck out here and say if you
>> don't 'need' to then don't. You are already on the best
>> kind of raid (if you can afford it) otherwise its going to
>> be a shed load of work to get it all working.
>> Peter
>> "They say that you only learn by mistake, does that mean I
>> am the worlds most learned man?"
>> Peter The Spate
>>
>>
>>
>> >--Original Message--
>> >
>> >"Peter The Spate" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:aa3601c4d6c9$62a38270$a601280a@.phx.gbl...
>> >> Having one big file has a bit of risk attached to it,
>> and
>> >> is slightly slower than an alternative.
>> >>
>> >> Firstly if that one file goes down then you will need to
>> >> restore the whole lot, which can take a bit of a time to
>> >> do, for safety its better to split the file onto
>> different
>> >> disk drives, that way if one goes you still have your
>> data
>> >> safely on another disk (or if you use raiding different
>> >> set of disks).
>> >>
>> >
>> >Won't you have to restore everything anyway? How do you
>> >keep data consistancy between the 2 or more files? If you
>> only
>> >restore one file surely the restored file will be out of
>> date compared to
>> >the still existing file or files. Unless I'm missing
>> something.
>> >
>> >> The second is performance. If you put some files on disk
>> >> drive C, and disk drive D you will have two different
>> >> disks read write heads working for you that will improve
>> >> your performance.
>> >>
>> >> You could increase performance even more by proper
>> raiding
>> >> and splitting the files. For instance if you have a
>> group
>> >> of tables that are constently been written to you can
>> set
>> >> the raid to be say 10 for those tables, but on a
>> different
>> >> set of disks configure it to be raid 5.
>> >
>> >At the moment the entire database is raid 10. If I was to
>> split
>> >the database into more than one file how would I go about
>> it?
>> >It contains approx 38,000 tables, would I have to assign
>> half to
>> >a different file group?
>> >
>> >> One point though is that usually having one file will do
>> >> however if you can get away with it, have more.
>> >>
>> >> Peter
>> >>
>> >> "The best minds are not in government. If any were,
>> >> business would steal them away."
>> >> Ronald Reagan
>> >>
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >I am looking after an SAP database on SQL 2000 and its
>> >> current size is
>> >> >approx 190Gb. Due to the original people who installed
>> >> the system years ago
>> >> >the database is stored in one file. My question is,
>> will
>> >> this cause me
>> >> >problems as the database size increases? I know it will
>> >> be fine size wise
>> >> >I'm thinking more of performance issues. The file is
>> on a
>> >> S.A.N so disk
>> >> >speed wise it is pretty good.
>> >> >
>> >> >thanks
>> >> >
>> >> >Gav
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>|||For what it is worth:
There is really no reason to have multiple files in your configuration,
unless it is the case that you have data that you can clearly identify
as read-only or that will not change (i.e. sales_archive_1994). if this
is the case, and it is the case that there is a lot of this data, then
you can greatly improve your backup strategy by moving these tables to
a different file (dump time, dump frequency, restore time, etc.).
if you are going to move data to a new file, the most effective method
is to create a clustered index for that table while specifying the new
filegroup. since the leaf level of the clustered index is the data
itself, it will physically move the object to the new filegroup. if you
do not need the clustered index, you can drop it when you are done. you
will have to rebuild all the non-clustered indexes on that table as
well (specifying the new filegroup), otherwise they will remain on the
primary.
there are other ways to move objects, but they are all extremely log
intensive. the above way is extremely fast.
hth,
hans|||i forgot. if you are going move data around, then once you are done,
make sure that you execute a dbcc shrinkfile() on the primary
filegroup, otherwise you will get no benefit from the movement.
hans