Database size question

[dchow]

Our database size is currently 4G and is incrementing at a rate of
45M/day. What is the max size of a SQL database? And what is the
size beyond which the server performance will start to go down?

[Daniel Morgan]

Re: Database size question

dchow wrote:
[color=blue]
>Our database size is currently 4G and is incrementing at a rate of
>45M/day. What is the max size of a SQL database? And what is the
>size beyond which the server performance will start to go down?
>
>
>[/color]
On what hardware?

From where I sit you've probably already passed that point you just
don't know it.

But until end-users start complaining I'd stay where you are while
getting ready to either
archive off what isn't required or move to Oracle, DB2, or Informix on a
UNIX platform.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[dchow]

Re: Database size question

SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
disk. Didn't have the server and CPU model with me.


On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
<damorgan@x.was hington.edu> wrote:
[color=blue]
>dchow wrote:
>[color=green]
>>Our database size is currently 4G and is incrementing at a rate of
>>45M/day. What is the max size of a SQL database? And what is the
>>size beyond which the server performance will start to go down?
>>
>>
>>[/color]
>On what hardware?
>
> From where I sit you've probably already passed that point you just
>don't know it.
>
>But until end-users start complaining I'd stay where you are while
>getting ready to either
>archive off what isn't required or move to Oracle, DB2, or Informix on a
>UNIX platform.[/color]

[Daniel Morgan]

Re: Database size question

dchow wrote:
[color=blue]
>SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>disk. Didn't have the server and CPU model with me.
>
>
>[/color]
You say 50G SCSI hard disk like it is a single disk? Not a good
situation with any RDBMS.

I'd say your are already runnnig far under what other RDBMS products
could do. But does
it matter? Not until end-users start to notice a difference or some
process starts taking longer
than its window allows.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Simon Hayes]

Re: Database size question

dchow <dchow@hotmail. com> wrote in message news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=blue]
> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> disk. Didn't have the server and CPU model with me.
>
>
> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> <damorgan@x.was hington.edu> wrote:
>[color=green]
> >dchow wrote:
> >[color=darkred]
> >>Our database size is currently 4G and is incrementing at a rate of
> >>45M/day. What is the max size of a SQL database? And what is the
> >>size beyond which the server performance will start to go down?
> >>[/color][/color][/color]

<snip>

Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
that most people will ever need to consider. In practice the
limitations are storage capacity and your ability to manage and
backup/restore the database. A 4GB database is not large, and 45MB per
day is a growth of about 16GB per year - 20GB is not particularly
large either.

It's not possible to say when performance will go down - it depends on
the load you place on the server. You can use Performance Monitor and
other tools to monitor CPU, disk access, memory use etc. to see if
there's a bottleneck somewhere. Having a single 50GB hard drive seems
rather limiting, if that's what you have - disk space is cheap, so
most people can afford to get extra disks and use RAID (or perhaps a
SAN/NAS) to improve performance by spreading the databases across
multiple disks.

In any case, discussing the size of a database or the hardware it runs
on usually isn't as important as how well it has been designed. If you
have a well designed database which is properly indexed and accessed
using well-written code, then it will perform and scale well up to
very large amounts of data. If you don't, then you can have
performance problems with even small amounts of data.

Simon

[Greg D. Moore \(Strider\)]

Re: Database size question


"dchow" <dchow@hotmail. com> wrote in message
news:mo6rpv4007 jep364somgt2p1q rhf2kl02p@4ax.c om...[color=blue]
> Our database size is currently 4G and is incrementing at a rate of
> 45M/day. What is the max size of a SQL database? And what is the
> size beyond which the server performance will start to go down?[/color]

Max size is "huge" far larger than anything you'll encounter (heck, just
buying the disks to reach the max size will break most budgets).

That's not a large DB by any means.

What's more important is your hardware and indexes.

If you have fast drives (and keep your log files on a separate physical set
of drives) and good indices, things should be fairly fast.

Our largest DB is 78 GB and that's under SQL 7.0.

Our largest under SQL 2K is 37G and growing and still very fast.

[color=blue]
>[/color]

[Greg D. Moore \(Strider\)]

Re: Database size question


"dchow" <dchow@hotmail. com> wrote in message
news:mo6rpv4007 jep364somgt2p1q rhf2kl02p@4ax.c om...[color=blue]
> Our database size is currently 4G and is incrementing at a rate of
> 45M/day. What is the max size of a SQL database? And what is the
> size beyond which the server performance will start to go down?[/color]

Max size is "huge" far larger than anything you'll encounter (heck, just
buying the disks to reach the max size will break most budgets).

That's not a large DB by any means.

What's more important is your hardware and indexes.

If you have fast drives (and keep your log files on a separate physical set
of drives) and good indices, things should be fairly fast.

Our largest DB is 78 GB and that's under SQL 7.0.

Our largest under SQL 2K is 37G and growing and still very fast.

[color=blue]
>[/color]

[dchow]

Re: Database size question

Thanks Greg. That was what I hope to hear.

On Tue, 28 Oct 2003 15:43:02 GMT, "Greg D. Moore \(Strider\)"
<mooregr@greenm s.com> wrote:
[color=blue]
>
>"dchow" <dchow@hotmail. com> wrote in message
>news:mo6rpv400 7jep364somgt2p1 qrhf2kl02p@4ax. com...[color=green]
>> Our database size is currently 4G and is incrementing at a rate of
>> 45M/day. What is the max size of a SQL database? And what is the
>> size beyond which the server performance will start to go down?[/color]
>
>Max size is "huge" far larger than anything you'll encounter (heck, just
>buying the disks to reach the max size will break most budgets).
>
>That's not a large DB by any means.
>
>What's more important is your hardware and indexes.
>
>If you have fast drives (and keep your log files on a separate physical set
>of drives) and good indices, things should be fairly fast.
>
>Our largest DB is 78 GB and that's under SQL 7.0.
>
>Our largest under SQL 2K is 37G and growing and still very fast.
>
>[color=green]
>>[/color]
>[/color]

[dchow]

Re: Database size question

Thanks Greg. That was what I hope to hear.

On Tue, 28 Oct 2003 15:43:02 GMT, "Greg D. Moore \(Strider\)"
<mooregr@greenm s.com> wrote:
[color=blue]
>
>"dchow" <dchow@hotmail. com> wrote in message
>news:mo6rpv400 7jep364somgt2p1 qrhf2kl02p@4ax. com...[color=green]
>> Our database size is currently 4G and is incrementing at a rate of
>> 45M/day. What is the max size of a SQL database? And what is the
>> size beyond which the server performance will start to go down?[/color]
>
>Max size is "huge" far larger than anything you'll encounter (heck, just
>buying the disks to reach the max size will break most budgets).
>
>That's not a large DB by any means.
>
>What's more important is your hardware and indexes.
>
>If you have fast drives (and keep your log files on a separate physical set
>of drives) and good indices, things should be fairly fast.
>
>Our largest DB is 78 GB and that's under SQL 7.0.
>
>Our largest under SQL 2K is 37G and growing and still very fast.
>
>[color=green]
>>[/color]
>[/color]

[dchow]

Re: Database size question

Thanks Simon. If fact we have RAID. But because I am not a network
admin guy, I didn't know too much about it. All I know was that I have
50G on the data partition. Having learned that 45MB growth per day is
not particularly large made me more comfortable.


On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
[color=blue]
>dchow <dchow@hotmail. com> wrote in message news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=green]
>> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>> disk. Didn't have the server and CPU model with me.
>>
>>
>> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> <damorgan@x.was hington.edu> wrote:
>>[color=darkred]
>> >dchow wrote:
>> >
>> >>Our database size is currently 4G and is incrementing at a rate of
>> >>45M/day. What is the max size of a SQL database? And what is the
>> >>size beyond which the server performance will start to go down?
>> >>[/color][/color]
>
><snip>
>
>Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>that most people will ever need to consider. In practice the
>limitations are storage capacity and your ability to manage and
>backup/restore the database. A 4GB database is not large, and 45MB per
>day is a growth of about 16GB per year - 20GB is not particularly
>large either.
>
>It's not possible to say when performance will go down - it depends on
>the load you place on the server. You can use Performance Monitor and
>other tools to monitor CPU, disk access, memory use etc. to see if
>there's a bottleneck somewhere. Having a single 50GB hard drive seems
>rather limiting, if that's what you have - disk space is cheap, so
>most people can afford to get extra disks and use RAID (or perhaps a
>SAN/NAS) to improve performance by spreading the databases across
>multiple disks.
>
>In any case, discussing the size of a database or the hardware it runs
>on usually isn't as important as how well it has been designed. If you
>have a well designed database which is properly indexed and accessed
>using well-written code, then it will perform and scale well up to
>very large amounts of data. If you don't, then you can have
>performance problems with even small amounts of data.
>
>Simon[/color]

[dchow]

Re: Database size question

Thanks Simon. If fact we have RAID. But because I am not a network
admin guy, I didn't know too much about it. All I know was that I have
50G on the data partition. Having learned that 45MB growth per day is
not particularly large made me more comfortable.


On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
[color=blue]
>dchow <dchow@hotmail. com> wrote in message news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=green]
>> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>> disk. Didn't have the server and CPU model with me.
>>
>>
>> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> <damorgan@x.was hington.edu> wrote:
>>[color=darkred]
>> >dchow wrote:
>> >
>> >>Our database size is currently 4G and is incrementing at a rate of
>> >>45M/day. What is the max size of a SQL database? And what is the
>> >>size beyond which the server performance will start to go down?
>> >>[/color][/color]
>
><snip>
>
>Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>that most people will ever need to consider. In practice the
>limitations are storage capacity and your ability to manage and
>backup/restore the database. A 4GB database is not large, and 45MB per
>day is a growth of about 16GB per year - 20GB is not particularly
>large either.
>
>It's not possible to say when performance will go down - it depends on
>the load you place on the server. You can use Performance Monitor and
>other tools to monitor CPU, disk access, memory use etc. to see if
>there's a bottleneck somewhere. Having a single 50GB hard drive seems
>rather limiting, if that's what you have - disk space is cheap, so
>most people can afford to get extra disks and use RAID (or perhaps a
>SAN/NAS) to improve performance by spreading the databases across
>multiple disks.
>
>In any case, discussing the size of a database or the hardware it runs
>on usually isn't as important as how well it has been designed. If you
>have a well designed database which is properly indexed and accessed
>using well-written code, then it will perform and scale well up to
>very large amounts of data. If you don't, then you can have
>performance problems with even small amounts of data.
>
>Simon[/color]

[John Bell]

Re: Database size question

Hi

I don't think anyone has mentioned that you should have a maintenance plan
(or equivalent jobs) that backups up the database, checks integrity, and
shrinks the files.

John

If this is in place you should have the ability to recover in case of
disaster and
"dchow" <dchow@hotmail. com> wrote in message
news:bfjtpvsqke 2rc56chrvdvl8i0 c8jcpeccp@4ax.c om...[color=blue]
> Thanks Simon. If fact we have RAID. But because I am not a network
> admin guy, I didn't know too much about it. All I know was that I have
> 50G on the data partition. Having learned that 45MB growth per day is
> not particularly large made me more comfortable.
>
>
> On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
>[color=green]
> >dchow <dchow@hotmail. com> wrote in message[/color][/color]
news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=blue][color=green][color=darkred]
> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> >> disk. Didn't have the server and CPU model with me.
> >>
> >>
> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> >> <damorgan@x.was hington.edu> wrote:
> >>
> >> >dchow wrote:
> >> >
> >> >>Our database size is currently 4G and is incrementing at a rate of
> >> >>45M/day. What is the max size of a SQL database? And what is the
> >> >>size beyond which the server performance will start to go down?
> >> >>[/color]
> >
> ><snip>
> >
> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> >that most people will ever need to consider. In practice the
> >limitations are storage capacity and your ability to manage and
> >backup/restore the database. A 4GB database is not large, and 45MB per
> >day is a growth of about 16GB per year - 20GB is not particularly
> >large either.
> >
> >It's not possible to say when performance will go down - it depends on
> >the load you place on the server. You can use Performance Monitor and
> >other tools to monitor CPU, disk access, memory use etc. to see if
> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> >rather limiting, if that's what you have - disk space is cheap, so
> >most people can afford to get extra disks and use RAID (or perhaps a
> >SAN/NAS) to improve performance by spreading the databases across
> >multiple disks.
> >
> >In any case, discussing the size of a database or the hardware it runs
> >on usually isn't as important as how well it has been designed. If you
> >have a well designed database which is properly indexed and accessed
> >using well-written code, then it will perform and scale well up to
> >very large amounts of data. If you don't, then you can have
> >performance problems with even small amounts of data.
> >
> >Simon[/color]
>[/color]

[John Bell]

Re: Database size question

Hi

I don't think anyone has mentioned that you should have a maintenance plan
(or equivalent jobs) that backups up the database, checks integrity, and
shrinks the files.

John

If this is in place you should have the ability to recover in case of
disaster and
"dchow" <dchow@hotmail. com> wrote in message
news:bfjtpvsqke 2rc56chrvdvl8i0 c8jcpeccp@4ax.c om...[color=blue]
> Thanks Simon. If fact we have RAID. But because I am not a network
> admin guy, I didn't know too much about it. All I know was that I have
> 50G on the data partition. Having learned that 45MB growth per day is
> not particularly large made me more comfortable.
>
>
> On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
>[color=green]
> >dchow <dchow@hotmail. com> wrote in message[/color][/color]
news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=blue][color=green][color=darkred]
> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> >> disk. Didn't have the server and CPU model with me.
> >>
> >>
> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> >> <damorgan@x.was hington.edu> wrote:
> >>
> >> >dchow wrote:
> >> >
> >> >>Our database size is currently 4G and is incrementing at a rate of
> >> >>45M/day. What is the max size of a SQL database? And what is the
> >> >>size beyond which the server performance will start to go down?
> >> >>[/color]
> >
> ><snip>
> >
> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> >that most people will ever need to consider. In practice the
> >limitations are storage capacity and your ability to manage and
> >backup/restore the database. A 4GB database is not large, and 45MB per
> >day is a growth of about 16GB per year - 20GB is not particularly
> >large either.
> >
> >It's not possible to say when performance will go down - it depends on
> >the load you place on the server. You can use Performance Monitor and
> >other tools to monitor CPU, disk access, memory use etc. to see if
> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> >rather limiting, if that's what you have - disk space is cheap, so
> >most people can afford to get extra disks and use RAID (or perhaps a
> >SAN/NAS) to improve performance by spreading the databases across
> >multiple disks.
> >
> >In any case, discussing the size of a database or the hardware it runs
> >on usually isn't as important as how well it has been designed. If you
> >have a well designed database which is properly indexed and accessed
> >using well-written code, then it will perform and scale well up to
> >very large amounts of data. If you don't, then you can have
> >performance problems with even small amounts of data.
> >
> >Simon[/color]
>[/color]

[Greg D. Moore \(Strider\)]

Re: Database size question


"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:3f9f8a79$0 $9469$ed9e5944@ reading.news.pi pex.net...[color=blue]
> Hi
>
> I don't think anyone has mentioned that you should have a maintenance plan
> (or equivalent jobs) that backups up the database, checks integrity, and
> shrinks the files.[/color]

I personally do not favor shrinking the file. It adds overhead and as it's
just likely to grow again, there's usually not much point.

[color=blue]
>
> John
>
> If this is in place you should have the ability to recover in case of
> disaster and
> "dchow" <dchow@hotmail. com> wrote in message
> news:bfjtpvsqke 2rc56chrvdvl8i0 c8jcpeccp@4ax.c om...[color=green]
> > Thanks Simon. If fact we have RAID. But because I am not a network
> > admin guy, I didn't know too much about it. All I know was that I have
> > 50G on the data partition. Having learned that 45MB growth per day is
> > not particularly large made me more comfortable.
> >
> >
> > On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
> >[color=darkred]
> > >dchow <dchow@hotmail. com> wrote in message[/color][/color]
> news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=green][color=darkred]
> > >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> > >> disk. Didn't have the server and CPU model with me.
> > >>
> > >>
> > >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> > >> <damorgan@x.was hington.edu> wrote:
> > >>
> > >> >dchow wrote:
> > >> >
> > >> >>Our database size is currently 4G and is incrementing at a rate of
> > >> >>45M/day. What is the max size of a SQL database? And what is the
> > >> >>size beyond which the server performance will start to go down?
> > >> >>
> > >
> > ><snip>
> > >
> > >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> > >that most people will ever need to consider. In practice the
> > >limitations are storage capacity and your ability to manage and
> > >backup/restore the database. A 4GB database is not large, and 45MB per
> > >day is a growth of about 16GB per year - 20GB is not particularly
> > >large either.
> > >
> > >It's not possible to say when performance will go down - it depends on
> > >the load you place on the server. You can use Performance Monitor and
> > >other tools to monitor CPU, disk access, memory use etc. to see if
> > >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> > >rather limiting, if that's what you have - disk space is cheap, so
> > >most people can afford to get extra disks and use RAID (or perhaps a
> > >SAN/NAS) to improve performance by spreading the databases across
> > >multiple disks.
> > >
> > >In any case, discussing the size of a database or the hardware it runs
> > >on usually isn't as important as how well it has been designed. If you
> > >have a well designed database which is properly indexed and accessed
> > >using well-written code, then it will perform and scale well up to
> > >very large amounts of data. If you don't, then you can have
> > >performance problems with even small amounts of data.
> > >
> > >Simon[/color]
> >[/color]
>
>[/color]

[Greg D. Moore \(Strider\)]

Re: Database size question


"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:3f9f8a79$0 $9469$ed9e5944@ reading.news.pi pex.net...[color=blue]
> Hi
>
> I don't think anyone has mentioned that you should have a maintenance plan
> (or equivalent jobs) that backups up the database, checks integrity, and
> shrinks the files.[/color]

I personally do not favor shrinking the file. It adds overhead and as it's
just likely to grow again, there's usually not much point.

[color=blue]
>
> John
>
> If this is in place you should have the ability to recover in case of
> disaster and
> "dchow" <dchow@hotmail. com> wrote in message
> news:bfjtpvsqke 2rc56chrvdvl8i0 c8jcpeccp@4ax.c om...[color=green]
> > Thanks Simon. If fact we have RAID. But because I am not a network
> > admin guy, I didn't know too much about it. All I know was that I have
> > 50G on the data partition. Having learned that 45MB growth per day is
> > not particularly large made me more comfortable.
> >
> >
> > On 28 Oct 2003 01:01:20 -0800, sql@hayes.ch (Simon Hayes) wrote:
> >[color=darkred]
> > >dchow <dchow@hotmail. com> wrote in message[/color][/color]
> news:<inerpv06q snkscsnavmf249t 71aqv7ge7v@4ax. com>...[color=green][color=darkred]
> > >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> > >> disk. Didn't have the server and CPU model with me.
> > >>
> > >>
> > >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> > >> <damorgan@x.was hington.edu> wrote:
> > >>
> > >> >dchow wrote:
> > >> >
> > >> >>Our database size is currently 4G and is incrementing at a rate of
> > >> >>45M/day. What is the max size of a SQL database? And what is the
> > >> >>size beyond which the server performance will start to go down?
> > >> >>
> > >
> > ><snip>
> > >
> > >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> > >that most people will ever need to consider. In practice the
> > >limitations are storage capacity and your ability to manage and
> > >backup/restore the database. A 4GB database is not large, and 45MB per
> > >day is a growth of about 16GB per year - 20GB is not particularly
> > >large either.
> > >
> > >It's not possible to say when performance will go down - it depends on
> > >the load you place on the server. You can use Performance Monitor and
> > >other tools to monitor CPU, disk access, memory use etc. to see if
> > >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> > >rather limiting, if that's what you have - disk space is cheap, so
> > >most people can afford to get extra disks and use RAID (or perhaps a
> > >SAN/NAS) to improve performance by spreading the databases across
> > >multiple disks.
> > >
> > >In any case, discussing the size of a database or the hardware it runs
> > >on usually isn't as important as how well it has been designed. If you
> > >have a well designed database which is properly indexed and accessed
> > >using well-written code, then it will perform and scale well up to
> > >very large amounts of data. If you don't, then you can have
> > >performance problems with even small amounts of data.
> > >
> > >Simon[/color]
> >[/color]
>
>[/color]