October 31, 2008 at 10:40 am
Very good article. Can you explain what occasionally happens in my environment.
Full Recovery mode, SQL 2005
db full backup each evening
Transaction log backup at noon and 6 pm (users insist they are happy with this amount of risk)
Day 1, in sql\sql\\data the size of *.ldf is small compared to DB *.mdf
Day 2, user performs a large monthly update to db
Day 2 by end of day, both transaction log backups and full db backup have occured
Day 3, in sql\sql\data the size of *.ldf is now enormous and larger than *.mdf
Day 3, the transaction log backup from night before is large
Day 4 through many days, size of *.ldf stays enormous in relationship to *.mdf
Day 4 through many days, size of transaction log backup is normal small size
Why does *.ldf stay so large?
DB *.mdf size is typically < 1 gig but I have one db which is 12 gig and this happens also
What I end up doing out of frustration
A) taking full backup
b) set to simple recovery
c) run DBCC SHRINKFILE (name of logical log file)
d) set to full recovery
e) take full backup again
Note: within the SQL scheme of things most of the DB's in question are small.
I'm fairly sure there is a better way.
October 31, 2008 at 11:08 am
Rob Symonds (10/31/2008)
Great article. I find myself struggling to explain these concepts to people on a weekly basis. You've put everything very clearly.
Thanks. I mostly wrote it because I got tired of explaining the same thing over and over. I think the record was 4 times in one day.
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
October 31, 2008 at 11:13 am
xyzt (10/31/2008)
I'm fairly sure there is a better way.
Backup the tran log more often. It's not just about risk, it's about how big you want the log to grow. If it's growing too large, back it up more often.
If it's still growing, query sys.databases and look what the log_reuse_wait_desc column says. That will explain why the log file isn't been reused and why the log has to grow.
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
October 31, 2008 at 3:03 pm
GilaMonster (10/31/2008)
Bob Bridges (10/31/2008)
One thing I'm not absolutely clear on though... you mentioned the "daisy chain of logs" in recovery. If I have a full backup every night and have transaction log backups every 6 hours, in case of a recovery need, do I need every log backup along with the last full backup or only the latest log backup along with the last full backup?All of them. A log backup only contains the log entries since the previous log backup (or full backup if there is no previous log backup). To restore, you need the full backup and then all of the log backups, in sequence, since that full backup.
It's differential backups where only the latest is needed, because diffs contain the data changes since the last full backup.
I just want to clarify one thing here. Just because you only need the latest diff backup to restore to the current point in time - does not mean that you should overwrite or delete the previous diff backups.
Only get rid of previous backups once you are sure they have been moved off to other media.
Don't know how many times I have seen someone initialize the log backup/differential backup thinking they were okay because the were backing up the system every x hours.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 31, 2008 at 6:59 pm
xyzt (10/31/2008)
Very good article. Can you explain what occasionally happens in my environment.Full Recovery mode, SQL 2005
db full backup each evening
Transaction log backup at noon and 6 pm (users insist they are happy with this amount of risk)
You could still backup the log every hour. Then some day when they decide they really do need something something not covered by your noon and 6 pm plan, you're one step ahead.
November 2, 2008 at 4:04 am
In you article you state that:
"If is a requirement for point-in-time recovery and minimal or no data loss in the case of a disaster, then the database should be in full or bulk-logged recover model and regular log backups should be done. The frequency that the log backups get done at should be determined by the maximum acceptable data loss in the case of a complete disaster."
However quoting from MSDN http://msdn.microsoft.com/en-us/library/ms186229.aspx
Restrictions for Point-in-time Recovery
If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.
Kevin
CodeMinkey
November 2, 2008 at 5:53 am
All that means is that you can't use the STOPAT command if there was a bulk operation within the log backup that covers the time requested.
If log backups occur at 01:00, 01:30 and 02:00 and a bulk operation starts at 01:35 and finishes at 01:45, then the 02:00 log backup must be restored in their entirety, or not at all. So it's possible to recover the DB to 01:00, and time between 01:00 and 01:30, 01:30 or 02:00, but not to 01:50. That will fail because there's a bulk operation in that log backup.
Ok, not quite point-in-time, because if the DB fails at 01:45, the best possible recovery is 01:30. I was trying not to complicate matters too much, and bulk-logged can get complex, especially when considering what are bulk operations and what are not.
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
November 4, 2008 at 11:17 am
Brilliant article Gail.
Crisp, concise, easy to understand and to the point.
You have good narration capability.
Any thoughts of writing a book on SQL Server? 😉
Regards,
RSingh
November 4, 2008 at 11:32 am
Actually, I've just finished writing a short 'chapter' for a book. 😀
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
November 4, 2008 at 9:23 pm
When will that book will be published? Please inform us. 🙂
November 5, 2008 at 12:06 am
Anirban Paul (11/4/2008)
When will that book will be published? Please inform us. 🙂
I don't know yet. Also, not sure how much I'm supposed to say about it.
I'm only writing a very small section, and there are a large number of people contributing.
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
November 5, 2008 at 8:48 am
GilaMonster (11/5/2008)
Anirban Paul (11/4/2008)
When will that book will be published? Please inform us. 🙂I don't know yet. Also, not sure how much I'm supposed to say about it.
I'm only writing a very small section, and there are a large number of people contributing.
Don't forget 'A Single Drop of Water Helps to Swell the Ocean' 🙂
February 11, 2009 at 7:37 am
Hi Gail,
Can you help clarify, on the Microsoft site (http://msdn.microsoft.com/en-us/library/ms186259.aspx) when talking about the write ahead transaction log they state;
"Log records are written to disk when the transactions are committed"
This can't be true otherwise how could you rollback an uncommitted transaction which i understand to be a transaction without a closing commit statement in the transaction log? (simplified) All the transactions in the log would be committed?
When are the log records written to disk?
Microsoft are saying that the when a modification is made to a page in the buffer cache a log record is built in the log cache and that this is not written to disk until the transaction is committed.
I'm confused, my understanding was that the log record was written immediately to disk and the modified data page was written to disk when a CHECKPOINT occurs or when the memory space is required to load another data page?
Who is right, ME or Microsoft?!
Any comments?
(Great article by the way)
February 11, 2009 at 8:41 am
[/quote]
If it's still growing, query sys.databases and look what the log_reuse_wait_desc column says. That will explain why the log file isn't been reused and why the log has to grow.[/quote]
I did a query to sysdatabases but I couldn't find the log_reuse_wait_desc colum.
Could someone help me? Thanks
February 11, 2009 at 10:35 am
Not sysdatabases. The view that has the log_reuse_wait_descr column is sys.databases, a SQL 2005 system view.
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 15 posts - 31 through 45 (of 128 total)
You must be logged in to reply to this topic. Login to reply