transaction log full

Question

Post reply

transaction log full

striker-baba

SSCrazy

Points: 2170
More actions
April 23, 2010 at 4:11 pm

#220801

Hi all,
I have a database 'db1' that has its transactional log full and I checked for the free space available.
It gives 29% free in datafile
and -95% in log file.
and When I run a package on this DB Its giving me the error.
I tried shrinkfile for Tlogfile using gui, and it said -98% free space.
So, How do I get free space in my transactional log.
should I truncate or shrink my tlog,
If so How?

Viewing 15 posts - 1 through 15 (of 21 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

What recovery mode is the database in? Can you back up the log to another drive?

And then again, I might be wrong ...
David Webb

striker-baba SSCrazy Points: 2170 More actions · Answer 2

Yes,

I can back up the log. But, it is not necessary.

and the database is in full recovery mode.

But can u give me both the scenarios like

what should I do Incase if I need to back up the log

and case where I dont need the log backup.

thanks

baba

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

When the database is in full recovery mode, the log will grow until you back it up, at which point, it will remove the committed transactions and reuse the space it has taken up or be available to be shrunk manually. Until those transactions are backed up, you can't reclaim the space. Once the log is backed up, it won't shrink back to its original size, but it will have space available internally for new transactions. To get it back to the original size, you need to back it up and then shrink it.

You can truncate the log, but then you lose the ability to restore from a backup and use the information in the truncated log to roll forward. If the database is in full recovery mode, the ability to roll forward must have been important at some point, so I wouldn't recommend truncating the log.

And then again, I might be wrong ...
David Webb

striker-baba SSCrazy Points: 2170 More actions · Answer 4

Thanks you david,

The information is really help full.

My Sr.dba says we can truncate the log no backup is required.

can you suggest me the best practice for truncating the log.

My plan is to put the database in simple recovery mode and then shrink the log file.

what do you suggest.?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

I'm not sure there is a 'best practice', but here's a link to a Microsoft article that, I think, covers what you want to do.

http://support.microsoft.com/kb/272318

Good luck.

And then again, I might be wrong ...
David Webb

striker-baba SSCrazy Points: 2170 More actions · Answer 6

yes, this is the information I was looking for..

thanks very much bro.

SQL_Hunt SSC-Dedicated Points: 33486 More actions · Answer 7

Try to free up space in the drive. What is the recovery model of the database? If possible, attach one more log file to a seperate drive where space available or stop sql server and move the ldf file to a drive where space is available.

Thanks.

Gail Shaw SSC Guru Points: 1004494 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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 9

striker-baba (4/23/2010)
I can back up the log. But, it is not necessary.
and the database is in full recovery mode.

if the database is in full recovery model then log backups are vital. What backup regime\jobs do you have in force against this database at present?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

striker-baba (4/23/2010)
My plan is to put the database in simple recovery mode and then shrink the log file.

Why do you want to put it in SIMPLE just for truncating log ?, then what would happen when you again put it in FULL model.

Spent sometime with this link

http://support.microsoft.com/kb/873235

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

homebrew01 SSC Guru Points: 55454 More actions · Answer 11

Assuming it's a production database, do you understand the disaster recover risks of switching to simple ?

striker-baba SSCrazy Points: 2170 More actions · Answer 12

I had a suggestion to put the database in simple recovery mode and then shrink the transaction log file

I dont know what impact does it has on the size of the file.

I would like to know its impacts both gud and bad..!!!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

GilaMonster (4/24/2010)
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

homebrew01 SSC Guru Points: 55454 More actions · Answer 14

striker-baba (4/25/2010)
I had a suggestion to put the database in simple recovery mode and then shrink the transaction log file
I dont know what impact does it has on the size of the file.
I would like to know its impacts both gud and bad..!!!

I don't think you are getting good advice from your dba.

Have you read Gail's article ? Have you read other documentation about transaction logs and recovery models ? There are different choices to make depending on different situations such as development or production environment, and how tolerant you business is for potential data loss (1 day's business, or just the last 10 minutes ...)