Full transaction log

[anorthcote]

Hi,

My database runs a weekly import job followed by several stored procedures to re-format and calculate on the imported data. This job has been running weekly for a couple of years now without any problems.

The data I import each week has grown a lot over the last few weeks and I am now getting the following error on setp 58 of my sheduled job

Msg 9002, Sev 17: The log file for database 'SZ' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000]

I am running this job at 6:15 on a Sunday morning and have the maintenance job running at 2:00 on the Sunday morning. I would have thought that running the maintenance job before my scheduled job would have cleared the transaction log sufficiently. The scheduled job I have does do a lot of processing and I have re-written the step it is erroring on to make it more efficient but I'm still getting the error.

My HDD space is not short so how can I extend the transaction log or do something to stop it erroring. My recovery is set to simple as I don't need the trasaction logs to recover the database. I don't understand what is going on!

 

[memdiscount]

Hello;

There are couple of things you can do:

1- You need to set the database properties to set the file growth of transaction log by 10 % automatically. Which is called Autogrowth of the file, means when there is a need to increase the file size due to heavy processing then the size will be increased automatically. I think that is not set so that when the file size reaches its maximum size which is set right now, then you get this error.

2- Truncate the Transaction Log, take complete backup of the database which will truncate the log.

3- You can also perform some database file shrinking.

I hope option 1 will resolve this issue.

Thanks

 

[anorthcote]

Thanks for that...

1- my properties were already set to automatically grow the log file by 10%

2- Surely my maintenance plan would truncate the log? Is there anyway I can truncate the log without taking a backup?

3- I have tried shrinking the database file but it didn't get much smaller so I don't think that will help.


If you have any further thoughts I'd be pleased to hear them.

Cheers

 

[SQLSister]

Depending on the size of what you are importing 10% may not allow it to grow fast enough.

Yes you can truncate the log without doing a backup, this is not a recommended procedure unless you have no disk space left. The command to do this is part of the backup log command (at least in SQL 2000, anyone know if this has changed in 2005?). Look in BOL for syntax.

Are you in fact backing up the log and not just the database? Backing up the database does not free up speace in the log only backing up the log can do that.

If you are not out of space on the disk, shrinking the log is not what you want to do (it will just have to grow even farther to accomodate the workload.) backing up the log is a better chopice as it will leave the log file the same size but willfree up the space in it to be reused. YOu want you r log to stay at about the size you need to accommodate your normal level of transactions.

"NOTHING is more important in a database than integrity." ESquared

 

[anorthcote]

You've hit the nail on the head, so to speak...

I wasn't backing up the transaction log as part of the maintenance plan. I've changed that and will see how it goes.

Is it worth increasing the growth % as well?

Thanks a lot.

 

[SQLSister]

Good thing you have started backing up the log as without doing so, your log would have grown until there was no space at all on the disk.

I'd hold the increasing grwoth percentage until you see if the other is sufficient. Unless you know you are doing a lot of importing of very large files.

"NOTHING is more important in a database than integrity." ESquared