Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup/Recovery Mode question Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 7:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:07 PM
Points: 9, Visits: 18
Hi all,

We have a 75GB hard drive dedicated to logs. Some time at night the logs for our databases grow to a total of 60-65GB. I am suggesting that we add more space to the drive (we are in a VM environment, so it will be easy to do so). The DBA at my job is saying that we change recovery model from FULL to SIMPLE in the middle of the night on our databases and then shrink the logs and then immediately do a full backup. He says that using this method there will not be any data loss and we can rely on the backups. Is that true? Also, is this approach recommended by MS or anyone?

Our databases will almost double in size by the end of the year. I think the best approach is to add more space to the log drive, but the DBA says that he would rather do the steps I mentioned above. How would you deal with this situation?

Thanks in advance.

Post #1510472
Posted Thursday, October 31, 2013 7:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:55 PM
Points: 17,728, Visits: 15,593
afshin.khorram (10/31/2013)
Hi all,

We have a 75GB hard drive dedicated to logs. Some time at night the logs for our databases grow to a total of 60-65GB. I am suggesting that we add more space to the drive (we are in a VM environment, so it will be easy to do so). The DBA at my job is saying that we change recovery model from FULL to SIMPLE in the middle of the night on our databases and then shrink the logs and then immediately do a full backup. He says that using this method there will not be any data loss and we can rely on the backups. Is that true? Also, is this approach recommended by MS or anyone?

Our databases will almost double in size by the end of the year. I think the best approach is to add more space to the log drive, but the DBA says that he would rather do the steps I mentioned above. How would you deal with this situation?

Thanks in advance.



So why are the logs growing to that size? It sounds like a pretty small database and the log growth appears to be a lot for your environment in comparison to the data. But hard to tell with the limited information. Knowing why the log grows in the middle of the night is a pretty important piece of information.

While you may reduce the risk in regards to loss of data, you would be creating a gap in point in time recover-ability. That gap may violate your RPO depending on the duration for the shrink of the log file.

Personally I would err on the side of adding space and right-sizing the database files.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1510475
Posted Thursday, October 31, 2013 7:56 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:13 PM
Points: 592, Visits: 924
My question is why are you in the FULL recovery model in the first place? Is it to be able to have a point-in-time restore? Then you need to leave it Full Recovery. When it switches over to Simple you lose the ability to have a point-in-time recovery during that time. Is that okay with your business owners? If not, then leave it in FULL recovery.

Do you know what is causing your Tlog to grow at night? Is it due to index maintenance? Are you tlog backups not set to run over night? Are you loading large amounts of data? I would suggest that you figure out why the log is growing and see if there is anything you can do to limit the size of the growth during that time. Look for things like rebuilding your indexes regardless of fragmentation level (fulls for all indexes, etc.). Can you perform more frequent log backups during the time, etc.

Let us know what you find and then we can help you come up with a better plan of attack.

I can't simply say that you should add more space to your Tlog, but I can say that you need to make sure you don't run out of space. So, while you are investigating the source of the growth make sure you are keeping an eye on the amount of disk space that is available and be ready to act.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1510476
Posted Thursday, October 31, 2013 7:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:07 PM
Points: 9, Visits: 18
Thanks for your response.

I don't believe that the logs are growing much. There are about a dozen databases, the largest log file gets to be around 17GB.

So if I understand you correctly, the solution that is proposed by my DBA can lead to data loss?
Post #1510477
Posted Thursday, October 31, 2013 8:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:55 PM
Points: 17,728, Visits: 15,593
afshin.khorram (10/31/2013)
Thanks for your response.

I don't believe that the logs are growing much. There are about a dozen databases, the largest log file gets to be around 17GB.

So if I understand you correctly, the solution that is proposed by my DBA can lead to data loss?


The solution proposed by the DBA can lead to an inability to recover to a specific point in time. So you need to answer the fundamental questions of what is your RPO? Must you be in FULL recovery model? What is causing the log growth?

If you can answer these questions then more appropriate answers can be given to help with your scenario.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1510478
Posted Thursday, October 31, 2013 8:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:07 PM
Points: 9, Visits: 18
Thanks again.

This is a production system that is supporting several departments within our company. And yes, we need to be able to recover all the data, or as much of it as possible. The nightly jobs load a lot of data. I know that there is a maintenance plan that reindexes some indexes as well. So that's where the log growth comes from.
Post #1510479
Posted Thursday, October 31, 2013 8:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:55 PM
Points: 17,728, Visits: 15,593
Well, if it is the graphical maintenance plan, then that is a problem. That is a brute force rebuild and is hardly practical in most situations.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1510481
Posted Thursday, October 31, 2013 8:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:07 PM
Points: 9, Visits: 18
I will change the index rebuild tomorrow. But I doubt that the reindex/rebuild creates a lot of logs, since one of our databases that is 56GB and 80 indexes only generates about 10GB of logs at night, even with all the data loads (this database gets the majority of the data loads I believe...but I have to verify).
Post #1510483
Posted Thursday, October 31, 2013 8:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:13 PM
Points: 592, Visits: 924
SQLRNNR (10/31/2013)
Well, if it is the graphical maintenance plan, then that is a problem. That is a brute force rebuild and is hardly practical in most situations.


I agree with Jason, you should look into a more custom solution like Ola Hallengren's scrpits.

How is the data loaded in at night?




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1510485
Posted Thursday, October 31, 2013 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:07 PM
Points: 9, Visits: 18
There are a ton of SSIS packages that pull in data from various sources, some from mainframe output files, some from ftp sites, etc. They are bulk loaded, as far as I know.

Have you guys ever used a recovery model switch? Do you recommend it?
Post #1510486
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse