September 26, 2012 at 9:05 am
I am getting my Database log full which is causing space issue, my DB is in full recovery mode.
I take full DB backup at night. I don't take any differential or Log backup.
To clear the Log do I need to take Log backup, I assume taking full backup should automatically clear the log files.
September 26, 2012 at 9:21 am
Yes. In full recovery mode you MUST run log backups in order to clear the log. This won't shrink the log, but it will clear it so that it can then be shrunk afterwards.
If for whatever reason you absolutely do not need point in time recovery then you should switch your recovery model to SIMPLE. I would think very carefully about this though since if you all of a sudden realize you do need point in time and your set to SIMPLE your going to be messed over.
You might consider looking into a combination of full, differential and log backups.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 26, 2012 at 9:28 am
Thanks
September 26, 2012 at 11:35 am
I've got a detailed blog post on what's going on here[/url]. As noted, you need to backup the log file. You can change the database to simple recovery and then you don't need to worry about it, but then you lose the ability to do a point in time recovery. Ask your business people how much data they're prepared to lose. If that number is zero or anything less than one full day's worth of data, then you need log backups.
I've got additional discussion here[/url] with some other links to more information on log backups.
"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
September 26, 2012 at 11:59 am
Please read through these - Managing Transaction Logs[/url]
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply