Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinking
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.
DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinkin
g
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of the
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?
Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?
|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:

> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>
>
|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...[vbcol=seagreen]
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of the
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:
> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> > We have a database consisting of one table that is used to store
> > application
> > reports. The reports are stored in a text field in the table. The size of
> > the
> > database is around 17 Gig and is set to auto-grow. The logical drive(part
> > of
> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> > table. We were able to insert reports that were 5K in size, but unable to
> > insert reports that were 6K. We deleted some of the reports from the table
> > and now things seem to be fine.
> > What can cause this?
>
>|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:
>> Gary,
>> Have you recently ran a DBCC CHECKDB on the database containing the table
>> or
>> a DBCC CHECKTABLE on the individual table itself?
>> As a side note, you might check out Reporting Services for the future of
>> report generation.
>> Reporting Services Home
>> http://www.microsoft.com/sql/reporting/default.mspx
>> HTH
>> Jerry
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>> > We have a database consisting of one table that is used to store
>> > application
>> > reports. The reports are stored in a text field in the table. The size
>> > of
>> > the
>> > database is around 17 Gig and is set to auto-grow. The logical
>> > drive(part
>> > of
>> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into
>> > the
>> > table. We were able to insert reports that were 5K in size, but unable
>> > to
>> > insert reports that were 6K. We deleted some of the reports from the
>> > table
>> > and now things seem to be fine.
>> > What can cause this?
>>

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of th
e
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:

> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table
or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>
>|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...[vbcol=seagreen]
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:
>

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les |||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les
Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les
|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les :)Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)

Insert or update into a Image column causes data to double in size

We are experiencing problems inserting or updating image fields from
one table to another in SQL Server.

When we do this what ever size of file we insert is doubled in size
when it is inserted into the destination table.

This happens in insert and update queries, and if we use DTS.

Any help would be greatly appreciatedskyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are experiencing problems inserting or updating image fields from
> one table to another in SQL Server.
> When we do this what ever size of file we insert is doubled in size
> when it is inserted into the destination table.
> This happens in insert and update queries, and if we use DTS.
> Any help would be greatly appreciated

I'm afraid that it's difficult to help, since you don't disclose very
many details. Could you provide the code you use to copy the fields?

What is the typical size of the data you copy?

What you mean by "file ... is doubled in size"? What file? The database
file? But then you talk about inserting the file? How do you know
that it doubles in size?

Sorry for asking so many questions, but while you know very well what is
going on at your site, I know next to nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are storing documents such as PDFs, word documents etc in a object
field in a table. When we transfer the the contents from one table to
another using even a simple

INSERT INTO tblTest (ObjectField)
SELECT ObjectField
FROM tblTest2

The data in the destination is exactly doubled in size when you load
the document again ie when you save a 1Mb document into the table
transfer it to the second table it becomes 2Mb.

Hope this makes it more clear.|||skyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are storing documents such as PDFs, word documents etc in a object
> field in a table. When we transfer the the contents from one table to
> another using even a simple
> INSERT INTO tblTest (ObjectField)
> SELECT ObjectField
> FROM tblTest2
> The data in the destination is exactly doubled in size when you load
> the document again ie when you save a 1Mb document into the table
> transfer it to the second table it becomes 2Mb.

How do you deduce that?

It would help if you could provide a complete script that demonstrates
the problem. That would be a script that creates the table, populates
the first, and the loads the data into other, and then somehow gets
the size of the documents. I realise that including a 1MB object in
a script is not realistic, but the table pub..pub_info includes an
image column. Maybe you can build a repro script from that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp