x

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

paws27284 gravatar image

paws27284
373 27 32 34

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
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

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(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

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(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

SuperDBA gravatar image

SuperDBA
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1948
x939

asked: Sep 13, 2011 at 02:02 PM

Seen: 1366 times

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