Table size too large when importing from text file

#1

I am having a weird problem when I try to import a text file into SQL Server
2000 using DTS. Only happens I import into an existing table that has various
field types, such as nvarchar, float, decimal, datetime, etc., as appropriate
for each field. After the import, the table size on disk is VERY large. Where
the source file is about 60 mb, for example, the resulting SQL table is 19
gb! This does not happen if I simply import into a table where all fields are
nvarchar(8000) and then do the conversion inside SQL Server. When I look at
the resulting table, it looks normal -- just takes up way too much space on
disk.

What am I missing?! Thanks!

#2

Is the table simply fragmented? DBCC SHOWCONTIG()

How are you arriving at the table size?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

"Vlad Kozlovsky" <VladKozlovsky (AT) discussions (DOT) microsoft.com> wrote

Quote:

I am having a weird problem when I try to import a text file into SQL
Server
2000 using DTS. Only happens I import into an existing table that has
various
field types, such as nvarchar, float, decimal, datetime, etc., as
appropriate
for each field. After the import, the table size on disk is VERY large.
Where
the source file is about 60 mb, for example, the resulting SQL table is 19
gb! This does not happen if I simply import into a table where all fields
are
nvarchar(8000) and then do the conversion inside SQL Server. When I look
at
the resulting table, it looks normal -- just takes up way too much space
on
disk.

What am I missing?! Thanks!

#3

I am looking at "Table Info" tab in Taskpad. I also checked the size of the
physical .MDF file.

Importing 172000 records took about 1 hour and increased MDF file size by 19
GB. This is on a new Itanuim-2 box with 64 GB RAM and a super-fast SAN.

Thanks for showcontig tip.

"Allan Mitchell" wrote:

Quote:

Is the table simply fragmented? DBCC SHOWCONTIG()

How are you arriving at the table size?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

#4

When importing then people tend to remove indexes.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

"Vlad Kozlovsky" <VladKozlovsky (AT) discussions (DOT) microsoft.com> wrote

Quote:

I am looking at "Table Info" tab in Taskpad. I also checked the size of the
physical .MDF file.

Importing 172000 records took about 1 hour and increased MDF file size by
19
GB. This is on a new Itanuim-2 box with 64 GB RAM and a super-fast SAN.

Thanks for showcontig tip.

"Allan Mitchell" wrote:

Is the table simply fragmented? DBCC SHOWCONTIG()

How are you arriving at the table size?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

#5

This table has no indexes.

"Allan Mitchell" wrote:

Quote:

When importing then people tend to remove indexes.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

#6

Make sure that the bloat is not in the log.

Add a CI to the table.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

"Vlad Kozlovsky" <VladKozlovsky (AT) discussions (DOT) microsoft.com> wrote

Quote:

This table has no indexes.

"Allan Mitchell" wrote:

When importing then people tend to remove indexes.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com

#7

Vlad,

What is the growth option for the database?

Ilya

"Vlad Kozlovsky" <VladKozlovsky (AT) discussions (DOT) microsoft.com> wrote

Quote:

I am having a weird problem when I try to import a text file into SQL
Server
2000 using DTS. Only happens I import into an existing table that has
various
field types, such as nvarchar, float, decimal, datetime, etc., as
appropriate
for each field. After the import, the table size on disk is VERY large.
Where
the source file is about 60 mb, for example, the resulting SQL table is 19
gb! This does not happen if I simply import into a table where all fields
are
nvarchar(8000) and then do the conversion inside SQL Server. When I look
at
the resulting table, it looks normal -- just takes up way too much space
on
disk.

What am I missing?! Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Table size too large when importing from text file

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Table size too large when importing from text file - 10-14-2004 , 12:05 PM

Re: Table size too large when importing from text file - 10-14-2004 , 01:40 PM

Re: Table size too large when importing from text file - 10-14-2004 , 02:21 PM

Re: Table size too large when importing from text file - 10-14-2004 , 02:29 PM

Re: Table size too large when importing from text file - 10-14-2004 , 02:33 PM

Re: Table size too large when importing from text file - 10-14-2004 , 02:37 PM

Re: Table size too large when importing from text file - 10-15-2004 , 07:28 AM