October 31, 2013 at 7:18 pm
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.
October 31, 2013 at 7:53 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2013 at 7:56 pm
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.
October 31, 2013 at 7:58 pm
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?
October 31, 2013 at 8:05 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2013 at 8:08 pm
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.
October 31, 2013 at 8:10 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2013 at 8:25 pm
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).
October 31, 2013 at 8:29 pm
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[/url].
How is the data loaded in at night?
October 31, 2013 at 8:32 pm
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?
October 31, 2013 at 9:44 pm
So the index rebuild job ran about 40 minutes ago. I verified that on the 56GB database it pushed the logs to 10GB. On another database, that is 95GB, with 68 indexes, it generated also 10GB. I verified that the logs have already been backed up and the files are currently empty. By tomorrow morning the second database will have a 17GB log, so the data load will take 17GB of log space by tomorrow morning. And again, 17GB is not much log space, specially on our system that loads a lot of data.
So far, if I understand correctly, you both have said that if we need to restore our databases to the last transaction, then we need to have the databases in FULL recovery mode at all times (and not switch recovery modes)? Is that correct? Please advise, thank you.
October 31, 2013 at 9:47 pm
That would be correct.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2013 at 10:59 pm
afshin.khorram (10/31/2013)
Have you guys ever used a recovery model switch? Do you recommend it?
To bulk-logged recovery for loads and index rebuilds sometimes. To from full to simple and back, never as a regular operation and very, very seldom as an ad-hoc.
See - http://www.sqlservercentral.com/articles/Administration/75461/ and http://www.sqlservercentral.com/articles/Recovery+Model/89664/
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 1, 2013 at 4:25 am
You havent mentioned in any of ur post that you are managing the logs ? for full recovery .. regular Tlog will be required .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 1, 2013 at 10:13 am
Thanks again for all of your input guys!
Assuming that the log growth is normal, that the databases need to be in FULL recovery mode so that the backup chain is not broken, and we need to back up all the logs, what would you do in our situation? Would you add more disk space to the log drive? Please keep in mind that our data is about to almost double in size.
Thanks in advance. Please give me answers so that non-DBA person can understand.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply