June 5, 2009 at 7:07 am
I have a SQL 2005 and my NOC keeps reporting that the t-log has reached it's capacity. I go in and truncate the log but 2 weeks later - the same thing. My boss said just set it to circular log, I am trying to figure out how to do that, any help would be appreciated
June 5, 2009 at 7:20 am
Is it getting backed up, i.e are you doing transaction log and DB backups?
June 5, 2009 at 7:52 am
Sounds like your database is using the full recovery model but you aren't doing any transaction log backups. What is your currnet backup strategy?
June 5, 2009 at 9:17 am
Circular logging is something that the windows event logs do. The SQL transaction log is not an event or error log. If you're in full recovery and are not managing the log (as it sounds) it will grow until it fills the disk.
There are two options. Set the DB to simple recovery mode, meaning SQL will manage the log itself. Downside of this is that point in time recovery is not possible. If the DB fails the best you will be able to do is restore the last full backup. If you're running backups once a day, that means losing up to a day's data. Will your business accept that?
Other option is to manage the log yourself by backing it up at regular intervals.
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
June 5, 2009 at 1:17 pm
Thanks for the replys, I have been going into SQL Studio management and backing up the database and then shrinking it, I have been having to do this once a week. It seems I am in full recovery mode and need to stay in this mode. Is there a way to automate the backing up and shrinking of the log file?
June 5, 2009 at 1:34 pm
rsaylors (6/5/2009)
Thanks for the replys, I have been going into SQL Studio management and backing up the database and then shrinking it, I have been having to do this once a week. It seems I am in full recovery mode and need to stay in this mode. Is there a way to automate the backing up and shrinking of the log file?
First, DON'T shrink the transaction log, it will only need to grow again. The best thing to do is setup maintenance plans for regularly scheduled full, differential, and transactions log backups. Properly scheduled, especially the t-log backups, will keep you transaction logs under control and keep you from having to shrink them unless something extraordinary occurs and causes the t-log to grow more than normal.
I believe the article that Gail pointed you toward should help you develop a solid backup policy. I'd also read about database and log backups in Books Online.
June 5, 2009 at 1:45 pm
Gail's article was a huge help in understanding how T-Logs work, and the various recovery models, I would like to be able to automate the backup of the T-Log which to my understanding is after I backup the log it is truncated, is this correct? and if so can someone explain how this is done?
June 5, 2009 at 1:48 pm
Look up maintenance plans in BOL. That is a good place to start.
June 5, 2009 at 2:34 pm
Lynn,
I am not trying to be snarky or anything - but I simply asked if someone could provide me with some help today with backing this log up and getting rid of the alerts from the NOC, and your response is for me to order a books online and read up on it? I thought forums such as these where a place where you would go for answers to questions. Any question can be answered with "Buy the book and read about it"
June 5, 2009 at 2:36 pm
I'm not telling you to buy ANY books. Books Online is the SQL Server Help file. While in SSMS, press F1, it will bring it up.
June 5, 2009 at 2:43 pm
I'd also like to suggest this blog entry as well, The Flip Side. It may also provide you with additional insight in how to deal with those of us you feel are being difficult.
June 5, 2009 at 2:43 pm
Maint plans are covered in quite a bit of detail in Books Online. Far more than Lynn or I could go into in a reasonable amount of time. If you don't have a local copy of SQL or a local copy of BoL, it is available online on Microsoft's MSDN site.
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
June 5, 2009 at 3:34 pm
rsaylors (6/5/2009)
Gail's article was a huge help in understanding how T-Logs work, and the various recovery models, I would like to be able to automate the backup of the T-Log which to my understanding is after I backup the log it is truncated, is this correct? and if so can someone explain how this is done?
Lest this slip through the cracks: NO, backup will not truncate the transaction log, nor should you want it to. Backing up the transaction log will allow it to be re-used.
If you backup the transaction log regularly and more often, then it should not need to grow as large. Once you have found that size that it needs to be with your activity levels and backup schedule, then you can set the transaction log to that size and you shouldn't have to worry about it after that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 5, 2009 at 3:38 pm
To add to what Gail said, "Books Online" or "BOL" is the help system that comes free with SQL Server and can normally be reached just by pressing the {f1} function key. It's incredibly useful for answer more than "simple" questions that have some pretty long answers. It's not uncommon to refer folks to "BOL" (Books Online) for questions that require extensive answers.
For a short answer, Barry is pretty much spot on.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2009 at 6:22 pm
Ack... my apologies... Lynn already pointed out the {f1} key. Sorry for the duplication of information.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply