How do I reuse Transactional Logs?

Question

How do I reuse Transactional Logs?

djiang

SSC-Addicted

Points: 425
More actions
December 12, 2007 at 9:12 am

#182007

Hi Guys,
I have to find a way to re-use transactional logs. I'm looking at the sys.databases and log_reuse_wait_desc.
How do I set it so that it'll re use the transactional logs. Log backup will need the log to be backed up before the logs can be reused. I want it so that it'll reuse the logs without any backups.
Any help would be appreciated. The boss is on me like white on rice........
Regards,
Dave

Viewing 12 posts - 1 through 11 (of 11 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Logs are automatically reused, but you need to do log backups. Log backups are a part of any SQL Server installation. You can set up the simple recovery mode, which basically clears the log every 5 minutes, but that means you lose some recovery capabilities, which is not recommended for production systems.

djiang SSC-Addicted Points: 425 More actions · Answer 2

Thanks for your quick reply.

I don't think this DB is in production. It's in a test environment and I need to do what you said. Change to simple recover mode which refreshes the log ever 5 mins. How would i change to that? Sorry for my ignorance, I'm still fairly new at this stuff.

Regards,

Dave

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

ALTER DATABASE dbname

SET RECOVERY SIMPLE

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

djiang SSC-Addicted Points: 425 More actions · Answer 4

djiang

SSC-Addicted

Points: 425

December 12, 2007 at 9:32 am

#759479

Thanks a lot you guys!!! 🙂

djiang SSC-Addicted Points: 425 More actions · Answer 5

hi Guys,

It turns out that the simple recovery mode isn't good enough. The boss wants to know if you can reuse the logs without backing them up. I'm not sure what that really means.

I told him about the simple recovery. He said he wants to reuse the logs. Does that make any sense to any of you?? I'm confused......:w00t:

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

Me too. I've never heard the phrase "reuse logs" applied to SQL Server.

I mean, we "reuse" them all the time. The same log file is cleaned out, whether you're in simple, bulk logged or full and reused.

Are they angling for some type of recovery?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

djiang SSC-Addicted Points: 425 More actions · Answer 7

Well I think he means for sys.databases log_reuse_wait_desc

NOTHING - doesn't reuse and just keeps adding to the log file (please correct me if i'm wrong)

LOG_BACKUP - backs up the log before cleaning it. (please correct if i'm wrong)

He wants to know if we can reuse the log files without the backup. I think changing the db to simple recovery will do it, but he's telling me that won't reuse the transactional logs. I will email him right now. I've never heard of that term either......but then again, there's a lot I havn't heard of. 🙂

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

The key word and tricky phrase to take away from the description of this column in books online is: "... reuse of transaction log space..." This means that it will be reclaiming and reusing the space within a log. This occurs after the committed transactions are cleaned out by a checkpoint in simple recovery or by a log backup in the other recovery models. It always reuses the files. You don't have to go around creating settings or messing with stuff for it to reuse the files. To reclaim the space, you need the log to truncate. That's all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

djiang SSC-Addicted Points: 425 More actions · Answer 9

Thanks again =)

You guys should get paid for this 🙂

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 10

"Should get paid" ?

I thought Steve was paying us for this?

Hey, get that guy over here...

😎

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 11

I think your boss does not understand how a log works. It's not like a log file written by IIS. It's a circular file that's used to record the transactions before they are written to the data.

And you want to back them up. There's no discussion on that. If this is a production database, you back up the logs.