Hi DBA's -
Please comment on my understanding of this concept -
The current size of the database I am building is 321 GB. Of the 321 GB, 175 GB is the size of the actual data. The remainder of the size is the unallocated space (Space that is not reserved for any objects). I used the sp_spaceused to obtain the statistics.
When a full backup occurs it is the portion containing the data (175 GB) that gets backed up along with the size information of the overall database (321 GB).
Now say I want to test my backup solution on a test machine.
When a restore of the database would occur, the size of the restored database would be 321 GB and not 175 GB which is the size of the actual data. So restore the database successfully I would need a test machine that would accommodate the database size (321 GB) and not just the actual data size (175 GB).
Please add your expert comments.
Thanks.utterly true, the size of the backup set will be around 175 GB, but the size of restored db on test server will be exactly the same as on production machine.
mojza|||If you're looking to save space you could so in one of two ways, shrink your source database before you back it up - won't reduce the size of your backup file, but will reduce the size of the restored database - or just shrink the restored database. I'm not a big proponent of shrinking databases - if it needed that space once, it will typically need it again, if it's shrunk, it will have to spend processor and memory to get it back to the proper size. Just my $.02 :)
Nick
Showing posts with label building. Show all posts
Showing posts with label building. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
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?
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
> news:209a901c45a06$a1f1f9b0$a601280a@.phx
.gbl...
when[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||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
>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=seagr
een]
size[vbcol=seagreen]
file[vbcol=seagreen]
65[vbcol=seagreen]
have[vbcol=seagreen]
grow.[vbcol=seagreen]
>
>.
>
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?
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
> news:209a901c45a06$a1f1f9b0$a601280a@.phx
.gbl...
when[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||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
>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=seagr
een]
size[vbcol=seagreen]
file[vbcol=seagreen]
65[vbcol=seagreen]
have[vbcol=seagreen]
grow.[vbcol=seagreen]
>
>.
>
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?
>
>"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.
>
>.
>|||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...
> 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?
> >
> >
> >"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.
> >
> >
> >.
> >|||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
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
>> 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?
>> >
>> >
>> >"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.
>> >
>> >
>> >.
>> >
>
>.
>
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?
>
>"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.
>
>.
>|||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...
> 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?
> >
> >
> >"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.
> >
> >
> >.
> >|||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
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
>> 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?
>> >
>> >
>> >"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.
>> >
>> >
>> >.
>> >
>
>.
>
Wednesday, March 21, 2012
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.
>
>.
>
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.
>
>.
>
Subscribe to:
Posts (Atom)