| myaffee 2005-06-29, 11:23 am |
| 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_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!
|