February 24, 2011 at 12:08 pm
More would be helpful. This is an excellent article but a little clarification for me (newbie status) would be very helpful. What causes the active portion of the log to grow while the backup is underway -- transaction activity on the database? If the database is idle while being backed up, and backup duration is quite long (because of slow tape and slow server i/o) will the active log still increase in size and if so by how much, in relative terms?
Thanks!
February 24, 2011 at 12:58 pm
the log can only grow because of database activity.
log entries will only be truncated at the end of a log backup (see Gails previous replies)
So even if the tape is slow and there is no db activity, your log will not grow.
Gail has also published other articles at SSC regarding recovery models. Certainly advisable reading material.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2011 at 7:47 pm
Chet Xemoka (2/24/2011)
What causes the active portion of the log to grow while the backup is underway -- transaction activity on the database?
Yup. Any form of data modification is logged.
If the database is idle while being backed up, and backup duration is quite long (because of slow tape and slow server i/o) will the active log still increase in size and if so by how much, in relative terms?
If the database is completely and totally idle, absolutely no transactions, no queries, then the log won't grow because nothing is getting written to the log.
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
March 15, 2011 at 9:59 am
I am a definite newbie!
If a database is currently set to FULL recovery and no backup log is being performed but I wish to switch to SIMPLE, should I do anything with the log file before I commit this change?
March 15, 2011 at 10:15 am
First, why switch to simple? If you don't have a good reason and don't understand the implication, don't do it.
Second, no. Just switch to simple. However, you are then limited to recovering from the last full backup.
March 15, 2011 at 10:32 am
Thanks Steve, The reason is I forgot that the default is full when the database is created. We had a small crisis when the log disk got full over the weekend. We didn't need point in time recovery so we backed up the db and then switched to simple but the transaction log was still large. We ended up using dbcc shrinkfile to reduce the size. After reviewing, I found that there are other databases that were apparently created the same way before I came along.
March 15, 2011 at 10:36 am
As long as you are sure you don't need point in time recovery, you did the right thing.
I would argue that most people don't think they need point in time recovery until something breaks. Then they are upset that all the work they did today is gone.
If you don't need it, fine, but be sure. Don't guess, and don't necessarily ask the question "how much data can you lose". Ask them how they feel if the db crashes 5 min before the next full.
March 15, 2011 at 11:27 am
When switching to simple recovery model, you also lose the ability to perform file or filegroup or page level restores. For example, if I had a corrupted page in a database, I could restore the affected page from the full backup and then apply all of the log backups since that time to bring the page up to date.
Nonetheless, there are definitely times when simple recovery is fine to use so long as you are aware of the ramifications.
September 26, 2011 at 5:53 am
Thanks Gail, for detailed article. This is very useful and this goes to briefcase 🙂
January 29, 2012 at 7:33 am
I'm having a hard time on where to start.. I'm a little overwhelmed.
January 29, 2012 at 7:33 am
I'm having a hard time on where to start.. I'm a little overwhelmed.
January 29, 2012 at 8:58 am
Feel free to post questions in the forums, we can probably help you figure things out.
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
January 29, 2012 at 9:31 am
I appreciate the guidance and education:w00t:.. Learning process for me. Thank you
March 29, 2012 at 8:48 pm
Hi,
We got this error:
-- Msg 9002, Level 17, State 4, Procedure sp_xxx, Line nn
-- The transaction log for database 'dbdbdbdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
It seems to have been caused by lack of physical space on the Server's drive in this case so I suggest check that as well.
Regards
Walter
March 29, 2012 at 9:04 pm
Running out of space on the drive is a symptom, not the cause. You need to look past the symptom to see what caused it. Possible root causes would be lack of log backups, long running transaction, or replication/CDC/mirroring not processing the transactions.
Viewing 15 posts - 46 through 60 (of 76 total)
You must be logged in to reply to this topic. Login to reply