October 2, 2007 at 12:24 pm
We have our server on simple recovery mode (SQL 2005). There is a daily package which dumps 2-3 gb of data on the server every day. The log file is growing enormously even tough it is in simple recovery and we take a full backup. After shrinking it to 10 mb and checking after the package has been executed it size is almost 4 gb again.
I have set the upper limit of the log file to 2 gb thinking the inactive transactions will be truncated if the file is filled with more than 70% as documented in BOL, however the package failed. The log file says "The transaction log for database 'ABC' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases."
In the sys.databases table the values for that DataBase were log_reuse_wait_desc = 'ACTIVE_TRANSACTION'
log_reuse_wait = 4
I would like to know how can the tran-log file be truncated automatically i.e. checkpoints
I am looking for appropriate suggestions from you friends.
...Kumar
October 2, 2007 at 1:21 pm
The 2-3 GB of data that you are importing each day is going to require quite a bit of t-log space. Your import failed because you've limited the space. The only way to keep it from using so much space would be to batch your imports into smaller transactions.
October 2, 2007 at 1:50 pm
What i understand is break the import into multiple small packages, so that it is not a single big transaction on a whole, but a number of small transactions. i'l give that a try for sure.
Is there any way we can automate the checkpoint so that the log file does not grow enormous. Thanks for your advice.
...Kumar
October 2, 2007 at 1:56 pm
How are you importing the data, BCP, BULK INSERT?
October 2, 2007 at 2:00 pm
DTS package.... As the source is not MS SQL, we truncate the tables in the destination and load the data
...Kumar
October 2, 2007 at 2:02 pm
DTS or SSIS? If i remember, in DTS you can set your batch size, but not sure if you can edit an imported (upgraded) DTS package in SSIS.
In the OLE DB Destination Editor in an SSIS package, you can also set the number rows in a batch.
October 2, 2007 at 2:06 pm
The packages are still in Legacy DTS. Can you please let me know how can we set the batch size in DTS. The packages are under Management-Legacy-DTS.
...Kumar
October 2, 2007 at 2:09 pm
Couldn't help there, hopefully some one else is more knowledgeable there. We still have have SQL Server 2000 database servers here and the developers still use EM to build and maintain them, as do I when I need to review them.
😎
October 2, 2007 at 2:15 pm
Thanks for your response Lynn. I think i need to do some web/forum mining and am sure i'l find some one who has faced this issue and posted it. Thanks a lot.
...Kumar
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply