question

paws27284 avatar image
paws27284 asked

Transaction log error SSIS 2005

Hi I'm trying to insert via SSIS 2005 bulk data into a table within 'my database' I get this error: [Bulk Insert Task] Error: An error occurred with the following error message: "The transaction log for database 'my database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". I discover that log_reuse_wait_desc = ACTIVE_TRANSACTION I execute DBCC OPENTRAN No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator. I issue the command BACKUP LOG MyDatabase WITH TRUNCATE_ONLY I then issue the command DBCC SHRINKFILE (MyDatabase_log, 10) WITH NO_INFOMSGS. I can see the file is now 10 MB. There is 63 GB available on the drive, so I don't think it is disk space. When I execute the task again, I get the same message. Now the log file is 1 GB. Any ideas... Help! Paws27284
sql-server-2005ssis
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

great news that we got you running again. When you get a moment can you come back and vote up the answers that helped and tick one that you think was most applicable. It give you and the answerers some karma points and shows other people that come across the same problem as you, what helped you get over the issue. Thanks.
1 Like 1 ·
Ya know..I looked at that but for some reason I didn't pick up on the "restricted growth" option. I fixed it and it ran BEAUTIFULLY! You guys rock! paws27284
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
Is the log set to AUTO grow? Check the database properties for this. Also, If you are trying to insert a large amount of data then increasing the tlog to a reasonable size would be beneficial.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
Check database log file properties and set log file auto grow on with reasonable size grow.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SuperDBA avatar image
SuperDBA answered
You may need to add another log file. Also make sure you still have space on the disk where the log file resides.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.