9GB Transaction Log: when is OK to start fresh, and how?

Question

9GB Transaction Log: when is OK to start fresh, and how?

Viewing 11 posts - 16 through 25 (of 25 total)

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

One thing to be aware of - the TRUNCATE_ONLY and NO_LOG options have been removed in SQL Server 2008. We tried to remove them in 2005 too but they go put back in again after Beta 2.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Yeah - thanks Paul. I actually had it in my head to add a note that truncate_only was going to be deprecated but got distracted by a darn work item! 🙂 I wish it WASN'T being deprecated tho! I find it useful and efficient. No need to fiddle with database settings, waste server capacity/disk space with a useless backup, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Joe B-478020 SSC Eights! Points: 966 More actions · Answer 3

seeing the poster mentioned starting over, getting the space back, etc., the following three T-SQL statements will do just that.

--this discards the log

backup log [MyDatabase] with no_log

--this shrinks the database and log files

dbcc shrinkdatabase([MyDatabase'], notruncate)

--this will release extra space to the operating system

dbcc shrinkdatabase([MyDatabase], truncateonly)

you should execute a full database backup immediately after doing this because you just flushed the transaction log down the toilet.

for the future, you do need to do what other posters have suggested. use simple recovery or implement a database maintenance plan that backs up the transaction log on a regular basis to keep it from growing out of control.

i hope SQL 2008 does not remove no_log functionality. i find that to be critical operation. in a situation where a log file has grown out of control, what do you do if there is no place to backup the log to because it's too large?

just my two cents,

joe

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

Good topic for a contraversial blog post. See BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it and SQL Server 2008: What's been removed in this version? BACKUP LOG WITH NO_LOG for one!

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Susan S SSCommitted Points: 1771 More actions · Answer 5

If " backup log [MyDatabase] with no_log " will be removed

and we use ' simple recovery backup

what happen if the DB need using Full backup recovery.

what is the solution for this?

I currently did this because my log is growing enormously around 90Gb

I suspect that this happen after re-index,re-organioze index etc.in maintenance plan.

Any suggestion for my problem?

Thanks,

Susan

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

I'm not sure I understand your question. Are you asking whether full backups are possible in SIMPLE recovery mode? Yes - but you cannot take log backups too so your points in time that you can restore to are limited to those provided by your full backups.

If your log is growing to 90GB then I'm guessing you're in FULL recovery mode and you're only taking full database backups. (Don't confuse 'FULL' recovery mode and 'full' database backup.) If you're currently relying on using the NO_LOG option to truncate the log, then you're not getting any of the benefits of being in FULL recovery mode so you may as well switch to SIMPLE recovery mode - and your log shouldn't grow so big (as long as you don't have a workload that produces transactions that large, of course).

Hope this helps.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Susan S SSCommitted Points: 1771 More actions · Answer 7

Sorry to confused you.

my recovery model is 'Full'.

in your article, you mention that one of way to avoid groing log file, we should switch to 'Simple' recovery.

however on my situation, we can't swicth to 'Simple' because this DB is important.

what I mean how do I prevent my log file grows but still in 'Full' recovery.

right the moment I am backing the transaction log every 15 minutes.

I notice that the log file is growing larger on Sun.

I suspect because I have a schedule for check DB Integrity, re-organize index, re-build index,update stats every Sun at 2am and my daily back up is @9pm.

do you think my maintenance plan strategy is wrong?

Advice me on this.

Thanks,

Susan

PS.

I shrink the log file, only occasitionally to reduce my physical drive to lower.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Please don't cross-post on multiple threads - thanks.

How large is your database, and how large is the largest table you're reindexing?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Susan S SSCommitted Points: 1771 More actions · Answer 9

DB is about 45Gb.

I don't know which table has the largest index.

the apps is not developed by us.

thanks,

Susan

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

index rebuilds will always cause t log growth and I'll generally leave the t log at the size it needs to accomodate this growth - however there are some things you can do.

Don't rebuild all your indexes within a transaction ; split them into batches with log backups between - this avoids changing database recovery mode.

Use selective index rebuilds instead of taking a blanket approach - it's really pointless rebuilding tables which are not fragmented.( you still might want to batch this and issue backup commands )

change the recovery model to simple - this won't actually solve your problem if the index rebuilds are in a transaction as you still need checkpoints to clear down the log inbetween rebuilds - I ran a series of interesting blogs over an outsourced data centre's attempts to deal with exactly this problem.

If you change recovery model then you need either a full of diff backup after you switch the database back to full recovery otherwise you'll invalidate t log backups ( something the data centre couldn't understand )

Either way you go the solution is to really not take a blanket approach to index rebuilds or leave the t log at it's max size. I'd really advise against using shrink commands which will produce ntfs fragmentation.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Susan S SSCommitted Points: 1771 More actions · Answer 11

Thank your for your info.

I try not to use shrink file.

I guess I was a bit surprise when the log file is growing very large.

I will monitor it again and perhaps I will leave it as it is.

the reason I am a bit curious before because when we haven't converted to 2005.... the log file doesn't really grows this large.