problem with large number of inserts

We have an application that is connecting to our SQL 2k server remotely via
ODBC and sending a large number of individual insert statements. Apparently
this is causing problems with the transaction filling up on the sql database
the inserts are being sent to. To solve the problem, we set the transaction
log to be able to grow to 2GB. It quickly filled that space. Then we set it
to unlimited growth. Over time it grew to 12GB which began to cause other
problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB bu
t
the remote app is not able to complete the inserts at that size. Truncate lo
g
on checkpoint is selected in the database properties.
I found some KB articles which referred to problems doing large updates but
they had to do with using the 'where' statement and we are doing inserts wit
h
no 'where' statement so it didn't seem to apply. Any ideas on how to solve
this problem?
Neither the Event Viewer nor the SQL Logs are offering much insight. No
errors are logged when the remote insert fails. However, the same database
appears to be failing to backup. During the weekly backup routine, we get
this in the Event Viewer:
"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
A subsequent backup operation may be slower/larger than normal."
We also get several variations of this message for both the Log and Database
:
3041 :
BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK
=
N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
We have 30 or so other databases, some larger some smaller, running on the
same SQL Server and none of them are having problems backing up. Thanks for
your help!Hi
Run a DBCC CHECKDB on this database and see what it reports.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"myaffee" wrote:

> We have an application that is connecting to our SQL 2k server remotely vi
a
> ODBC and sending a large number of individual insert statements. Apparentl
y
> this is causing problems with the transaction filling up on the sql databa
se
> the inserts are being sent to. To solve the problem, we set the transactio
n
> log to be able to grow to 2GB. It quickly filled that space. Then we set i
t
> to unlimited growth. Over time it grew to 12GB which began to cause other
> problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB
but
> the remote app is not able to complete the inserts at that size. Truncate
log
> on checkpoint is selected in the database properties.
> I found some KB articles which referred to problems doing large updates bu
t
> they had to do with using the 'where' statement and we are doing inserts w
ith
> no 'where' statement so it didn't seem to apply. Any ideas on how to solve
> this problem?
> Neither the Event Viewer nor the SQL Logs are offering much insight. No
> errors are logged when the remote insert fails. However, the same database
> appears to be failing to backup. During the weekly backup routine, we get
> this in the Event Viewer:
> "Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 361
7.
> A subsequent backup operation may be slower/larger than normal."
> We also get several variations of this message for both the Log and Databa
se:
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DI
SK =
> N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD
,
> NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
> We have 30 or so other databases, some larger some smaller, running on the
> same SQL Server and none of them are having problems backing up. Thanks fo
r
> your help!
>|||Mike,
Here's what it reported:
DBCC results for 'specialneeds'.
DBCC results for 'sysobjects'.
There are 245 rows in 6 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 283 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1167 rows in 25 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 220 rows in 19 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 571 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 2 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'leAuditCollectConfigVars'.
There are 3 rows in 1 pages for object 'leAuditCollectConfigVars'.
DBCC results for 'leAuditCollectDatabases'.
There are 59 rows in 1 pages for object 'leAuditCollectDatabases'.
DBCC results for 'leAuditCollectNotification'.
There are 1 rows in 1 pages for object 'leAuditCollectNotification'.
DBCC results for 'leAuditCollectAlerts'.
There are 5 rows in 1 pages for object 'leAuditCollectAlerts'.
DBCC results for 'affiliatelog'.
There are 0 rows in 1 pages for object 'affiliatelog'.
DBCC results for 'billing'.
There are 0 rows in 1 pages for object 'billing'.
DBCC results for 'configuration'.
There are 355 rows in 3 pages for object 'configuration'.
DBCC results for 'configuration1'.
There are 0 rows in 1 pages for object 'configuration1'.
DBCC results for 'coupons'.
There are 9 rows in 1 pages for object 'coupons'.
DBCC results for 'customerprices'.
There are 0 rows in 1 pages for object 'customerprices'.
DBCC results for 'customers'.
There are 1083 rows in 34 pages for object 'customers'.
DBCC results for 'downloads'.
There are 0 rows in 1 pages for object 'downloads'.
DBCC results for 'gifts'.
There are 0 rows in 1 pages for object 'gifts'.
DBCC results for 'hackers'.
There are 1 rows in 1 pages for object 'hackers'.
DBCC results for 'languages'.
There are 671 rows in 6 pages for object 'languages'.
DBCC results for 'mycompany'.
There are 0 rows in 1 pages for object 'mycompany'.
DBCC results for 'ordertracking'.
There are 0 rows in 1 pages for object 'ordertracking'.
DBCC results for 'pinnumbers'.
There are 0 rows in 1 pages for object 'pinnumbers'.
DBCC results for 'prodcategories'.
There are 2891 rows in 8 pages for object 'prodcategories'.
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'prodfeatures'.
There are 0 rows in 1 pages for object 'prodfeatures'.
DBCC results for 'projects'.
There are 0 rows in 1 pages for object 'projects'.
DBCC results for 'quantitydiscounts'.
There are 0 rows in 1 pages for object 'quantitydiscounts'.
DBCC results for 'registrant'.
There are 0 rows in 1 pages for object 'registrant'.
DBCC results for 'registryitems'.
There are 0 rows in 1 pages for object 'registryitems'.
DBCC results for 'reviews'.
There are 0 rows in 1 pages for object 'reviews'.
DBCC results for 'savedcarts'.
There are 3 rows in 1 pages for object 'savedcarts'.
DBCC results for 'searchresults'.
There are 0 rows in 1 pages for object 'searchresults'.
DBCC results for 'shipmethods'.
There are 1 rows in 1 pages for object 'shipmethods'.
DBCC results for 'suppliers'.
There are 0 rows in 1 pages for object 'suppliers'.
DBCC results for 'tblaccess'.
There are 40 rows in 2 pages for object 'tblaccess'.
DBCC results for 'tbllog'.
There are 682 rows in 11 pages for object 'tbllog'.
DBCC results for 'orders'.
There are 997 rows in 71 pages for object 'orders'.
DBCC results for 'tbluser'.
There are 2 rows in 1 pages for object 'tbluser'.
DBCC results for 'templates'.
There are 0 rows in 1 pages for object 'templates'.
DBCC results for 'oitems'.
There are 2093 rows in 42 pages for object 'oitems'.
DBCC results for 'ipaddress'.
There are 8 rows in 1 pages for object 'ipaddress'.
DBCC results for 'privlabelproducts'.
There are 0 rows in 1 pages for object 'privlabelproducts'.
DBCC results for 'privatelabels'.
There are 1 rows in 1 pages for object 'privatelabels'.
DBCC results for 'privlabelproducts1'.
There are 32 rows in 3 pages for object 'privlabelproducts1'.
DBCC results for 'privatelabels1'.
There are 1 rows in 1 pages for object 'privatelabels1'.
DBCC results for 'privlabelproducts3'.
There are 0 rows in 1 pages for object 'privlabelproducts3'.
DBCC results for 'privatelabels3'.
There are 1 rows in 1 pages for object 'privatelabels3'.
DBCC results for 'categories'.
There are 39 rows in 1 pages for object 'categories'.
DBCC results for 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
There are 0 rows in 0 pages for object 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
DBCC results for 'spneeds_calendar'.
There are 10 rows in 1 pages for object 'spneeds_calendar'.
DBCC results for 'calendar'.
There are 13 rows in 1 pages for object 'calendar'.
DBCC results for 'spneeds_level'.
There are 12 rows in 1 pages for object 'spneeds_level'.
DBCC results for 'spneeds_author'.
There are 2892 rows in 18 pages for object 'spneeds_author'.
DBCC results for 'spneeds_subject'.
There are 39 rows in 1 pages for object 'spneeds_subject'.
DBCC results for 'spneeds_product'.
There are 2890 rows in 69 pages for object 'spneeds_product'.
DBCC results for 'disability_link_categories'.
There are 6 rows in 1 pages for object 'disability_link_categories'.
DBCC results for 'disability_link_images'.
There are 3 rows in 1 pages for object 'disability_link_images'.
DBCC results for 'disability_links'.
There are 16 rows in 1 pages for object 'disability_links'.
DBCC results for 'discount_codes'.
There are 1 rows in 1 pages for object 'discount_codes'.
DBCC results for 'spneeds_messages'.
There are 1 rows in 1 pages for object 'spneeds_messages'.
DBCC results for 'EmailContest'.
There are 434 rows in 8 pages for object 'EmailContest'.
DBCC results for 'products3'.
There are 3251 rows in 144 pages for object 'products3'.
DBCC results for 'products1'.
There are 3258 rows in 145 pages for object 'products1'.
DBCC results for 'products'.
There are 3258 rows in 151 pages for object 'products'.
DBCC results for 'affiliates'.
There are 1 rows in 1 pages for object 'affiliates'.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'specialneeds'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Run a DBCC CHECKDB on this database and see what it reports.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "myaffee" wrote:
>