March 27, 2014 at 3:54 pm
Hi All,
I have a odd scenario, For example.
Say database is Full Recovery Model, No Log backups Configured , Then Obviously TLog grows till disk space. Here is my questions on this scenario.
1. Since No Log Backup there is no Checkpoint in full recovery model, then the dirty pages in memory do not flushed to the disk untill a checkpoint is made ? My questions is do the all the dirty pages or pages that been changed will ever committed to data file ?
2. Does the Full backups made in this scenario will have the most update data till the time of backup ? Since Full backup just backs up the datafile what happens to the data that been changed in RAM ?
3. For example, I have a database created in year 2000 and I backup daily full till date that is 2014 but never did tran log backup and hence the log grows but the data changed will ever push to the disk or no ?
I'm assuming when the memory got filled, based on the age of the pages , the dirty pages are pushed to disk when though the checkpoint is never happend for that particular database. Please correct me if I'm wrong
March 27, 2014 at 5:18 pm
Well, you are wrong. Checkpoint will occur. In fact one of the factors of that cause checkpoint is database backup. Of course there are other things that cause checkpoint. The backup was mentioned because of your question. The main reason of checkpoint is the configuration option "recovery interval", which instructs the server that if it evaluates that recovery process will take more then a specific time, it will run a checkpoint (the default is 1 minute). You can read more about it in http://technet.microsoft.com/en-us/library/ms189573.aspx%5B/url%5D
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 27, 2014 at 5:43 pm
@SQLFRNDZ (3/27/2014)
Hi All,I have a odd scenario, For example.
Say database is Full Recovery Model, No Log backups Configured , Then Obviously TLog grows till disk space. Here is my questions on this scenario.
1. Since No Log Backup there is no Checkpoint in full recovery model, then the dirty pages in memory do not flushed to the disk untill a checkpoint is made ? My questions is do the all the dirty pages or pages that been changed will ever committed to data file ?
Checkpoint in Simple Recovery occurs when the log is 70% full by default (um - no idea if this is configurable, I doubt it, but I don't actually know - but if you have a DBA who is setting hat and not taking log backups, I don't want a whiff of what they're smoking!). However (as I suspect you know) this does not clear the log. It will continue to grow as more transactions are added.
2. Does the Full backups made in this scenario will have the most update data till the time of backup ? Since Full backup just backs up the datafile what happens to the data that been changed in RAM ?
No - that's not what happens. It is complicated. I can't describe it better than this from Paul Randall - read everything he's written, and his wife and crew http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/
3. For example, I have a database created in year 2000 and I backup daily full till date that is 2014 but never did tran log backup and hence the log grows but the data changed will ever push to the disk or no ?
Yes. But never do this.
I'm assuming when the memory got filled, based on the age of the pages , the dirty pages are pushed to disk when though the checkpoint is never happend for that particular database. Please correct me if I'm wrong
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 27, 2014 at 6:07 pm
Adi Cohn-120898 (3/27/2014)
Well, you are wrong. Checkpoint will occur. In fact one of the factors of that cause checkpoint is database backup. Of course there are other things that cause checkpoint. The backup was mentioned because of your question. The main reason of checkpoint is the configuration option "recovery interval", which instructs the server that if it evaluates that recovery process will take more then a specific time, it will run a checkpoint (the default is 1 minute). You can read more about it in http://technet.microsoft.com/en-us/library/ms189573.aspx%5B/url%5DAdi
Thank you for clarification ! I knew recovery Interval setting and what it does but confused how it acts when in described scenario. So you are saying irrespective of recovery model, checkpoint comes every one minute or minimum of certain MB of data in tran log recorded.
March 27, 2014 at 6:14 pm
andrew gothard (3/27/2014)
@SQLFRNDZ (3/27/2014)
Hi All,I have a odd scenario, For example.
Say database is Full Recovery Model, No Log backups Configured , Then Obviously TLog grows till disk space. Here is my questions on this scenario.
1. Since No Log Backup there is no Checkpoint in full recovery model, then the dirty pages in memory do not flushed to the disk untill a checkpoint is made ? My questions is do the all the dirty pages or pages that been changed will ever committed to data file ?
Checkpoint in Simple Recovery occurs when the log is 70% full by default (um - no idea if this is configurable, I doubt it, but I don't actually know - but if you have a DBA who is setting hat and not taking log backups, I don't want a whiff of what they're smoking!). However (as I suspect you know) this does not clear the log. It will continue to grow as more transactions are added.
2. Does the Full backups made in this scenario will have the most update data till the time of backup ? Since Full backup just backs up the datafile what happens to the data that been changed in RAM ?
No - that's not what happens. It is complicated. I can't describe it better than this from Paul Randall - read everything he's written, and his wife and crew http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/
3. For example, I have a database created in year 2000 and I backup daily full till date that is 2014 but never did tran log backup and hence the log grows but the data changed will ever push to the disk or no ?
Yes. But never do this.
I'm assuming when the memory got filled, based on the age of the pages , the dirty pages are pushed to disk when though the checkpoint is never happend for that particular database. Please correct me if I'm wrong
Of course, Prevention is better .. I have seen a database like this recently with one of my client configured in FULL and never backup for log, I have created log backup job and wondered how it was surviving all these years and while analyzing my self I got these questions in my mind.
Thanks for your time.
March 27, 2014 at 6:32 pm
@SQLFRNDZ (3/27/2014)
andrew gothard (3/27/2014)
@SQLFRNDZ (3/27/2014)
Hi All,I have a odd scenario, For example.
Say database is Full Recovery Model, No Log backups Configured , Then Obviously TLog grows till disk space. Here is my questions on this scenario.
1. Since No Log Backup there is no Checkpoint in full recovery model, then the dirty pages in memory do not flushed to the disk untill a checkpoint is made ? My questions is do the all the dirty pages or pages that been changed will ever committed to data file ?
Checkpoint in Simple Recovery occurs when the log is 70% full by default (um - no idea if this is configurable, I doubt it, but I don't actually know - but if you have a DBA who is setting hat and not taking log backups, I don't want a whiff of what they're smoking!). However (as I suspect you know) this does not clear the log. It will continue to grow as more transactions are added.
2. Does the Full backups made in this scenario will have the most update data till the time of backup ? Since Full backup just backs up the datafile what happens to the data that been changed in RAM ?
No - that's not what happens. It is complicated. I can't describe it better than this from Paul Randall - read everything he's written, and his wife and crew http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/
3. For example, I have a database created in year 2000 and I backup daily full till date that is 2014 but never did tran log backup and hence the log grows but the data changed will ever push to the disk or no ?
Yes. But never do this.
I'm assuming when the memory got filled, based on the age of the pages , the dirty pages are pushed to disk when though the checkpoint is never happend for that particular database. Please correct me if I'm wrong
Of course, Prevention is better .. I have seen a database like this recently with one of my client configured in FULL and never backup for log, I have created log backup job and wondered how it was surviving all these years and while analyzing my self I got these questions in my mind.
Thanks for your time.
Hey, you're very welcome. I'm sure that you'll enjoy learning from the SQLSkills crew as much as I do. Also http://www.brentOzar.com, http://www.sqlblog.com http://www.simpleTalk.com are excellent sources. plus SQLInTheWild - GilaMonster (Gail Shaw)'s blog
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 27, 2014 at 6:59 pm
@SQLFRNDZ (3/27/2014)
Of course, Prevention is better .. I have seen a database like this recently with one of my client configured in FULL and never backup for log, I have created log backup job and wondered how it was surviving all these years and while analyzing my self I got these questions in my mind.
Thanks for your time.
Sure, a well planned log backup job defined by the amount of data that is acceptable to lose (or disk space depending on the most restrictive option) is the best. However, a recovery plan should be kept in place and the client should be aware of the importance of log backups.
If no one will validate or handle the log backups, you'll end up with lots of wasted space when changing the database recovery model to simple would be enough.
As with most things, the best solution depends on the specific scenario.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply