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 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 but
they had to do with using the 'where' statement and we are doing inserts with
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_200506190202.B AK', 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 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 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 but
> they had to do with using the 'where' statement and we are doing inserts with
> 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_200506190202.B AK', 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!
>
|||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:
Showing posts with label connecting. Show all posts
Showing posts with label connecting. Show all posts
Wednesday, March 28, 2012
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:
>
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:
>
Friday, March 23, 2012
problem with identifier lengths when connecting Analysis Services to Oracle
Hi All,
I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:
Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0
Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.
I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.
Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?
Any help would be appreciated.
Kind regards,
Miles
You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:
<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>
<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>
Restart the server/tools
Note that this is not a supported technique but should unblock you...
HTH,
Akshai
|||Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)
For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;
c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl
for the server, and;
c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl
for visual studio.
Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.
Kind regards,
Miles
|||Dear All,I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.
The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;
c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl
for the server, and;
c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl
for visual studio.
Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||
Hi 4lb3rt,
Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.
Cheers,
Miles
|||Dear Miles,I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?
Thanks in advance.sql
problem with identifier lengths when connecting Analysis Services to Oracle
Hi All,
I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:
Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0
Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.
I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.
Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?
Any help would be appreciated.
Kind regards,
Miles
You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:
<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>
<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>
Restart the server/tools
Note that this is not a supported technique but should unblock you...
HTH,
Akshai
|||Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)
For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;
c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl
for the server, and;
c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl
for visual studio.
Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.
Kind regards,
Miles
|||Dear All,I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.
The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;
c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl
for the server, and;
c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl
for visual studio.
Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||
Hi 4lb3rt,
Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.
Cheers,
Miles
|||Dear Miles,I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?
Thanks in advance.|||I did not have this problem (identifier too long) until I tried to design aggregations with multiple partitions. None of my Oracle data exceeds the 30 character limit, so I'm kinda lost. It is new however, since I added some additional columns to my sql for the dsv and the partitions, but they do not exceed 30! I can process, I just can't design aggregations and I sure would like to. If anyone has any more ideas, let me know. AS 2005.
Subscribe to:
Posts (Atom)