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

more ▼

asked Sep 13, 2011 at 02:02 PM in Default

avatar image

433 34 41 43

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!


Sep 14, 2011 at 06:36 AM paws27284

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.

Sep 14, 2011 at 06:50 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Sep 13, 2011 at 06:34 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left

Check database log file properties and set log file auto grow on with reasonable size grow.

more ▼

answered Sep 13, 2011 at 09:53 PM

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

You may need to add another log file. Also make sure you still have space on the disk where the log file resides.

more ▼

answered Sep 14, 2011 at 08:21 AM

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 13, 2011 at 02:02 PM

Seen: 1698 times

Last Updated: Sep 13, 2011 at 02:02 PM

Copyright 2018 Redgate Software. Privacy Policy