Database Size

I am building a database and I can not explain the size
it is growing to. I made the size of the database file
100GB. I added 28,000 records with a length of 100
bytes. I added 70,000,000 records with a lenght of 65
bytes. I ran out of space in the database.
I assumed 3 or 4 times the record sizes for overhead when
planning my DB. But I still ran out of room. I have the
backup mode set to simple so the Log file doesn't grow.
Any thoughts would be appreciated.
Please post DDL, including indexes. Also, how are you inserting the data?
"DB" <daveblair-nospam-@.adelphia.net> wrote in message
news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.
|||DB,
Hmmm, you should only need a database of around 4-5Gb for that amount of
data. How are you inserting the data?
The log file WILL grow if you are inserting data in large batches. It
needs to grow the log file so that it can roll back the batch if it
fails for some reason (like hardware failure).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DB wrote:
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.
|||Here is the DDL for the tables. There are no indexes at
this time. The data is inserted by a VB program using an
ADO connection to the database. The general code is:
tblAlias.Open "tblAlias", cn3, adOpenDynamic,
adLockOptimistic
**Start loop for each record to add
tblAlias.AddNew
tblAlias!BTBID = rsBusiness!BTBID
tblAlias!State = rsPrs!State
tblAlias!BusID = rsPrs!BusID
tblAlias!SiteID = rsPrs!SiteID
tblAlias!Company = Trim(rsPrs!Company)
tblAlias!StreetName = Trim(rsBusiness!StreetName)
tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
tblAlias!Zip5 = rsBusiness!Zip5
tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
& Mid(rsBusiness!Company, 3, 2)
tblAlias!MatchAddress3 = ""
tblAlias.Update
**End loop
DDL for table definitions.
CREATE TABLE [dbo].[tblAlias] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress1] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress2] [char] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblWord] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Word] [char] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

>--Original Message--
>Please post DDL, including indexes. Also, how are you
inserting the data?[vbcol=seagreen]
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
when[vbcol=seagreen]
the
>
>.
>
|||Given that your tables have no indexes and your inserts are being done on a
row-by-row basis, I have no clue how it's possible that 70,000,000 rows of
data grew to over 100gb. Are you certain that no other processes are using
the database in question? Did all 70,000,000 rows get inserted, or did the
process stop before it reached completion?
<anonymous@.discussions.microsoft.com> wrote in message
news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...[vbcol=seagreen]
> Here is the DDL for the tables. There are no indexes at
> this time. The data is inserted by a VB program using an
> ADO connection to the database. The general code is:
> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
> adLockOptimistic
> **Start loop for each record to add
> tblAlias.AddNew
> tblAlias!BTBID = rsBusiness!BTBID
> tblAlias!State = rsPrs!State
> tblAlias!BusID = rsPrs!BusID
> tblAlias!SiteID = rsPrs!SiteID
> tblAlias!Company = Trim(rsPrs!Company)
> tblAlias!StreetName = Trim(rsBusiness!StreetName)
> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
> tblAlias!Zip5 = rsBusiness!Zip5
> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> & Mid(rsBusiness!Company, 3, 2)
> tblAlias!MatchAddress3 = ""
> tblAlias.Update
> **End loop
>
> DDL for table definitions.
> CREATE TABLE [dbo].[tblAlias] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress1] [char] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress2] [char] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblWord] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Word] [char] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> inserting the data?
> when
> the
|||Actually, that's my problem. This is a new stand alone
box. There are no users other than myself. I have
roughly 280,000,000 total records to add to the
database. It halted at the 70,000,000 or so because the
database was full.
I'm completely stumped. While I assume there is
significant storage "overhead" associated with database
records, even 2 or 3 times the size should be less than
20GB.
I have a work around - shrink the DB when it maxes out.
Get all that space back, then resume adding records. A
pain but I'll get there.
Thanks for your efforts.

>--Original Message--
>Given that your tables have no indexes and your inserts
are being done on a
>row-by-row basis, I have no clue how it's possible that
70,000,000 rows of
>data grew to over 100gb. Are you certain that no other
processes are using
>the database in question? Did all 70,000,000 rows get
inserted, or did the[vbcol=seagreen]
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
at[vbcol=seagreen]
an[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagreen]
size[vbcol=seagreen]
file[vbcol=seagreen]
65[vbcol=seagreen]
have[vbcol=seagreen]
grow.
>
>.
>