Transaction log is Full

Question

Post reply

Transaction log is Full

pxt_ce

SSC Eights!

Points: 830
More actions
December 1, 2009 at 10:41 pm

#137174

we are geeting alert at the time of execution of any query. because of "Transaction log is Full"
so what are the different ways to over come from this issue?
Please mention all the possibility solution here.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

- Win. SSCertifiable Points: 6790 More actions · Answer 1

Hi,

How about this ?

http://technet.microsoft.com/en-us/library/ms175495.aspx

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 2

http://support.verio.com/documents/view_article.cfm?doc_id=3696

Cheers,
- Win.

" Have a great day "

pxt_ce SSC Eights! Points: 830 More actions · Answer 3

DBCC SHINKDATABASE , can help us to over come this issue?

SQLbee Ten Centuries Points: 1009 More actions · Answer 4

The first thing you need to do is check whether log is being backed up through jobs or not. If they are not, you immediately need to take a log backup.

DBCC shrinkdatabase will not help because it is meant to release excess that is being used by the data / log files, not back it up.

In case backup of log does not release sufficient space, you would need to increase the log file size by either enabling autogrowth or giving it a specified amount of space to accomadate the excess logs.

Let me know if this helps, or if you need anything else

Regards

Akhil

- Win. SSCertifiable Points: 6790 More actions · Answer 5

1. Take a backup and do adding the log file to another disk drive.

2. Increase the log file autogrowth to 10% if have space.

3. shrink log file after taking a backup of logfile or go to 1st point.

How much is the db size and the log file size ??

Cheers,
- Win.

" Have a great day "

pxt_ce SSC Eights! Points: 830 More actions · Answer 6

Data file is : 5 GB appox and log file size is 4.541

Thank you for your support.

pxt_ce SSC Eights! Points: 830 More actions · Answer 7

Akhil Thank you ..

Can you please let me know , what should be the best way to maintain the LOG file so that can not create issue on production.

what should be the best way to do so?

Thanks for you time ..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLbee Ten Centuries Points: 1009 More actions · Answer 9

The answer is not straight forward. To be able to get to a optimized log file size, you would need to monitor it growth over a period of few days, especially during peak activity.

The first thing you need to do is setup an alert on the log at about 70% full, so in case it starts to fill up you proactively know what to do.

Second thing to do si to decide the frequency of the log backups on your server. Since the database is just 5 GB, i do not think the server would be much loaded unless you have other dbs there as well. Depending on the server load you can setup backup frequency and set an alert for this job as well, so that you don't have to wait till the log gets full each time.

all this done, you would need to give extra buffer space to your log file to grwo in case of unexpectedly high activity, like a contingency plan. Set the autogrowth in terms of the actual data size instead of % of its size. You can add another log file on another disk partition as well, if possible.