Database size > 250 Gig

We all know and hear that Sql 2000 is an enterprise
database now. However, stories ( rumors if you may ) still
make the rounds that people with any database in excess of
50G gives lots of problems. I've been asked to recommend a
database server in my organization for a database that
will start at 300G and is expected to reach 500G. I would
like for it to be MS Sql Server. However, I would really
like to know how many people on this board actually use
database's that large and whether Sql Server is really the
platform of choice for those VLDB's. We all love Sql
Server but Lets be honest here.I have working databases with over 1TB of data. I also have databases with
300GB throughput per month. A properly designed and maintained system can
handle that kind of data load with SQL Server. It won't run 'out of the
box' with little or no planning and maintenance, but it can handle it with a
competent DBA running the show.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack A" <jacka8@.excite.com> wrote in message
news:019101c3c323$88ba2c80$a101280a@.phx.gbl...
> We all know and hear that Sql 2000 is an enterprise
> database now. However, stories ( rumors if you may ) still
> make the rounds that people with any database in excess of
> 50G gives lots of problems. I've been asked to recommend a
> database server in my organization for a database that
> will start at 300G and is expected to reach 500G. I would
> like for it to be MS Sql Server. However, I would really
> like to know how many people on this board actually use
> database's that large and whether Sql Server is really the
> platform of choice for those VLDB's. We all love Sql
> Server but Lets be honest here.|||> We all know and hear that Sql 2000 is an enterprise
> database now. However, stories ( rumors if you may ) still
> make the rounds that people with any database in excess of
> 50G gives lots of problems.
I think it may be the case that "large" databases require a little more
knowledge to manage in general, rather than specifically with SQL Server.
We are running SQL Server 2000 on both Windows Server 2000 and Windows
Server 2003, and we have multiple databases in the 600 GB -> 1.5 TB range.
SQL Server itself has not given any extra hassles that we wouldn't expect to
see with any RDBMS (e.g. issues not relating specifically to database
technology, such as data transfer keeping up over a dodgy network
connection).
> We all love Sql Server but Lets be honest here.
SQL Server is more than capable of handling VLDB. Space management can be
an issue, as well as network connectivity to a SAN, and the length of time
required to perform reliable backups. But this would be no different if I
were using Oracle, DB2, etc. Do you have a "perfect" RDBMS in mind, that
isn't vulnerable to any of the issues associated with large data stores?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> were using Oracle, DB2, etc. Do you have a "perfect" RDBMS in mind, that
> isn't vulnerable to any of the issues associated with large data stores?
Heh, and as Geoff pointed out, that wouldn't require a DBA on staff to plan
and manage...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In general, SQL Server doesn't have any issue with
managing >200GB databases.
In addition to others' comments, there are some specific
DBMS issues you may want to know. In particular, if your
database is used very heavily 24x7 with very large tables,
you may run into the nasty problem of not being able to re-
org your indexes online. You could try DBCC INDEXDEFRAG
for some relief. It may or may not be an effective
solution for a particular situation.
Again, this may not be a concern for you.
Linchi
>--Original Message--
>We all know and hear that Sql 2000 is an enterprise
>database now. However, stories ( rumors if you may )
still
>make the rounds that people with any database in excess
of
>50G gives lots of problems. I've been asked to recommend
a
>database server in my organization for a database that
>will start at 300G and is expected to reach 500G. I would
>like for it to be MS Sql Server. However, I would really
>like to know how many people on this board actually use
>database's that large and whether Sql Server is really
the
>platform of choice for those VLDB's. We all love Sql
>Server but Lets be honest here.
>.
>|||Thanks a ton. Will keep you guys posted of how things go.
>--Original Message--
>> were using Oracle, DB2, etc. Do you have a "perfect"
RDBMS in mind, that
>> isn't vulnerable to any of the issues associated with
large data stores?
>Heh, and as Geoff pointed out, that wouldn't require a
DBA on staff to plan
>and manage...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>

Database Size

If I'm creating a db from an inport, and I know it will be large (400 gig),
is it faster to create the DB that size, or create a 1 gig DB and let it
auto grow by 1 gig at a time when it needs it during the import?
Or would it take the same amount of time?Steve wrote:
> If I'm creating a db from an inport, and I know it will be large (400 gig)
,
> is it faster to create the DB that size, or create a 1 gig DB and let it
> auto grow by 1 gig at a time when it needs it during the import?
> Or would it take the same amount of time?
>
>
If you plan to keep this database for a while, I would create it all at
one time. Your import will run faster, and, assuming you can create the
initial file as one contiguous file, you won't have disk fragmentation
to worry about later.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know the import would run faster, as it wouldn't have to pause to claim
more disk space. But would I be saving time over all or would it take the
same amount of time?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
creating a single 400 gig mdf file?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Steve wrote:
> I know the import would run faster, as it wouldn't have to pause to claim
> more disk space. But would I be saving time over all or would it take the
> same amount of time?
>
Overall, the entire growth + import process will probably take as long
as just initially creating the database that size. Fragmentation
resulting from the constant addition of 1GB chunks would be your concern
if you let it auto-grow. You'll end up with chunks of the database
scattered all over the disk, causing additional disk overhead when
retrieving data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Steve wrote:
> would creating the db with a 200 gig mdf and 200 gif ndf file be faster th
an
> creating a single 400 gig mdf file?
>
Probably not, because you're still initializing the same amount of
space. If you could create them simultaneously, on seperate I/O
channels, then yes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your help.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Steve wrote:
> Probably not, because you're still initializing the same amount of space.
> If you could create them simultaneously, on seperate I/O channels, then
> yes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing to consider about autogrow: you have no control over when the
growth happens - so a random insert/update could cause growth and IO delays
at a busy period for your server. Far better for you to manage the growth of
the server yourself.
On SQL 2005, you can setup the system such that file growth is instantaneous
(i.e. the file is not zeroed when its created or grown)
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:ee26VkopGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Thanks for your help.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
>

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig
available but Enterprise Manager errors with not enough disk space with any
attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.=20
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have =

---

16 gig available but Enterprise Manager errors with not enough disk =
space with any attempt greater than 4gig. any suggestions are very much =
welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have 16

---

gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive
is formatted to FAT32 much to my suprise

Database Size

If I'm creating a db from an inport, and I know it will be large (400 gig),
is it faster to create the DB that size, or create a 1 gig DB and let it
auto grow by 1 gig at a time when it needs it during the import?
Or would it take the same amount of time?Steve wrote:
> If I'm creating a db from an inport, and I know it will be large (400 gig),
> is it faster to create the DB that size, or create a 1 gig DB and let it
> auto grow by 1 gig at a time when it needs it during the import?
> Or would it take the same amount of time?
>
>
If you plan to keep this database for a while, I would create it all at
one time. Your import will run faster, and, assuming you can create the
initial file as one contiguous file, you won't have disk fragmentation
to worry about later.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know the import would run faster, as it wouldn't have to pause to claim
more disk space. But would I be saving time over all or would it take the
same amount of time?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
>> If I'm creating a db from an inport, and I know it will be large (400
>> gig), is it faster to create the DB that size, or create a 1 gig DB and
>> let it auto grow by 1 gig at a time when it needs it during the import?
>> Or would it take the same amount of time?
>>
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
creating a single 400 gig mdf file?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
>> If I'm creating a db from an inport, and I know it will be large (400
>> gig), is it faster to create the DB that size, or create a 1 gig DB and
>> let it auto grow by 1 gig at a time when it needs it during the import?
>> Or would it take the same amount of time?
>>
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Steve wrote:
> I know the import would run faster, as it wouldn't have to pause to claim
> more disk space. But would I be saving time over all or would it take the
> same amount of time?
>
Overall, the entire growth + import process will probably take as long
as just initially creating the database that size. Fragmentation
resulting from the constant addition of 1GB chunks would be your concern
if you let it auto-grow. You'll end up with chunks of the database
scattered all over the disk, causing additional disk overhead when
retrieving data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Steve wrote:
> would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
> creating a single 400 gig mdf file?
>
Probably not, because you're still initializing the same amount of
space. If you could create them simultaneously, on seperate I/O
channels, then yes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your help.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Steve wrote:
>> would creating the db with a 200 gig mdf and 200 gif ndf file be faster
>> than creating a single 400 gig mdf file?
> Probably not, because you're still initializing the same amount of space.
> If you could create them simultaneously, on seperate I/O channels, then
> yes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing to consider about autogrow: you have no control over when the
growth happens - so a random insert/update could cause growth and IO delays
at a busy period for your server. Far better for you to manage the growth of
the server yourself.
On SQL 2005, you can setup the system such that file growth is instantaneous
(i.e. the file is not zeroed when its created or grown)
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:ee26VkopGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Thanks for your help.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
>> Steve wrote:
>> would creating the db with a 200 gig mdf and 200 gif ndf file be faster
>> than creating a single 400 gig mdf file?
>>
>> Probably not, because you're still initializing the same amount of space.
>> If you could create them simultaneously, on seperate I/O channels, then
>> yes.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig available but Enterprise Manager errors with not enough disk space with any attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have =16 gig available but Enterprise Manager errors with not enough disk =space with any attempt greater than 4gig. any suggestions are very much =welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have 16
gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive is formatted to FAT32 much to my suprisesql