May 20, 2009 at 1:51 am
Krishna Potlakayala (5/20/2009)
Sadequl Hussain (5/19/2009)
Krishna Potlakayala (5/19/2009)
Sadequl,I am afraid these ARE options. At least better than stopping SQL Server.
On what basis you say that?
Well, I would be saying this because in my viewpoint, the option I specified is better than stopping the SQL Server. This is what the OP mentioned in his very first post - I just showed an alternate way.
But not breaking the log chain- You consider that as a better option than what OP have asked? I would still argue on that point that there is no 'better bad advise'..... either there is bad piece of advise or a good piece of advise.
-- I do not think the advise I gave was "bad"...it is just one of the options and a valid one. I do not see why keeping a log chain intact would be of higher priority than ensuring SQL is not turned off time and again, like the OP is asked to do. Yes, keeping the log backup chain intact is important when everything else is running okay - like the server is not being forced to restart every time the drive is full. To me, maximising up time is more important. Having said that, I am also not saying that backups should not been done - you would have full backups and you would have log backups. Granted, they will need to be rescheduled every time the chain breaks, but this will be the case only until the problem of long transactions is fixed. Hence my advice.
And I did mention about truncation breaking log chain - read before typing.
Yes, I read it and I was quite surprised to see the advise.
Well I was surprised because before advising about log chain breaking, you did not read what I said in the following paragraph. Not that I am fused though. I just thought you did not read the whole message before starting to answer.
Above
The poster did not say if it was a SQL 2008 system. In any case, the equivalent command in 2008 can be used.
Does not matter here in this forum AFAIK we try to give the best advise and as Steve says we are in a camp of education people rather than just giving the answers for their questions!!!!
-- Yes, we try to give the best possible advise and I do not mind learning - however, this thread started when someone wanted the solution for a problem he was facing.
You answered the question yourself.
-- Not sure what you meant...
As I could understand from the original comments - it is not possible to change the code at this stage that is posting so much data causing the log filling up. "they are resarching" was the response to his query. it is now up to the DBA to come up with something.
This is the OP's first post:
Hi All,
I want to stop SQL Server service as a job. How can I do that?
Thanks.
Second post:
Our transaction logs are growing because of very very large transactions from application. We have notified them a lot of times to break their transactions but they are still researching for that. They said that they can't afford more disk space on transaction log drive. We have shrink log job which runs every hour after transaction log backups but that don't buy much as there are many uncommited transactions on that log files and we can't shrink it. We have notified them that we will need to stop sql server service if it go more than 95% of disk size limit. We have setup an alert to notify us but as a further step we are planning to stop sql server service if database logs goes more than 95% of disk space.
This is the desicion by my seniors and they want me to follow this.
I am sure from this that he said they are researching but did not say " it's not possible for them to change". It's upto the DBA and yes he has better options rather than breaking the log chain doesn't he?
-- Well, you can see from his message, it is a decision by his seniors (assuming management). It is not impossible to change the code perhaps, but it does not seem he has enough time at hand to wait for that. And breaking the log chain is not something that causes the server to be inaccessible - stopping the server makes it so. I would say breaking the log chain when the log becomes full is perhaps a better solution (as long as the code is not changed to post smaller transactions) then restarting the service.
Same as above
Again, it is ONE of the options - either shrink the log file to a size or better, keep it with a fixed size.
Probably, you should put this in a better way. Shrink the log file but not as a maintenance job or frequently.
-- Well, I would say rather than shrinking the log file time and agin (which would create fragmentation), it is best that it is kept a fixed size.
Same as above
-- Not sure what you meant, I repeated what I mentioned before
I do not agree with the fact if the backup chanin is broken it is not easy to build it. if it was so, we would not have something called DR. You can take a full backup and then reschedule the log backup. All can be done from maintenance plan.
What do you think of breaking the log chain of a VLDB 1TB?? and how long would it take to backup the database? I did not get it when you are talking about DR? Could you please make it clear again for me?
-- DR - Disaster Recovery. In a perfect world, the log chain NEVER breaks, and backups NEVER fail. Unfortunately they do - even for 1 TB databases. and the backup process needs to be reinitialised. that is just a DBA's task.
Agreed
es restrict it - to stop it from hogging up the whole drive. If you have the logs of all databases in a single drive and the drive is full - NO other database log file can grow.
What do you reckon the result is going to be then? As I explained, it is better to have one database inaccessible than th whole server - and also, it is still a better option (although less than ideal) than restarting the SQL Server. restarting the SQL Server in a Production environment time and again for clearing logs / drives - is not something any good DBA would do. And I explained how the log file can be prevented from filling up - below. Finally, checking if a data / log file is filling up - when the data / log file has a finite size is fairly easy - you just need to write a script and action accordinly based on the results.
Why do you want to restrict the log file growth anyway? If you manage it properly the log file would not grow at the first instance and even if it grows then it means it needs space to grow. You cannot restrict the log file to grow, it would not be a smart database maintenance.
-- Yes, managing log files does not allow it to grow or become full. The OP's message actually showed that his current strategy is not preventing the log from hogging up the disk. I suggested restricting file growth because a lot of organisatiosn DO practice that - AND has measures to check file size, growth to ensure they do not fill up. In my opinion, a filled log file and and a filled drive causes two issues:
a) Database being inaccessible
b) Other databases having log files in the same drive being affected.
Agreed.You answered it yourself again. Needs to change his backup strategy not to BREAK the log chain.
I am afraid not - look at SQL 2005 and try to implement it.
Sorry, I did not get that?
-- This was in response to your text "Very wrong !!!!!!!!!" (see above). Also, I suggested you have a look at SQL 2005 to see if the method of implementing the jobs, alerts etc. can be done.
Sorry, Well know about jobs. but you should grow up to understand what i pointed out there.
-- Not sure if you should use the "growing up" bit here - if you cannot agree with someone else's approach, please do not try to use these terms. It is called discussion.
you suggested:
"Now create a job. The job will have single step - the BACKUP LOG...NO_LOG command. Specify this job as "response" to the alert. Also, ensure the alert notifies you as the operator."
To create a regular maintenance job with NO_LOG??
-- NOT a MAINTENANCE job (like regular database maintenance task) - a job that will be put into place to keep the log file from growing and filling up the drive and ultimately stopping the SQL Server. This job will be put into place TEMPORARILY - as a fix, until the application can be changed / tuned to post smaller batches of transactions / long running transactions. Once that is done, the regular log backup schedule can cope with it and keep the log cleared.
There are different ways to approach a problem - the approach I mentioned, was not "destructive" I am afraid.
I do apologize if that have offended you. But am afraid too it is. Please research more into it.
-- I am not offended at all - not even after your message had texts like "No these are not options at all." or "Very wrong !!!!!!!!!" or "You should rather think in a constructive way". A problem can have multiple solutions and each can be perfectly valid. I am sorry if my message offends you, but we all come to thdse boards to listen (well see too be precise) and to learn, not to attack and prove superiority. I would suggest you do some research on your part as well.
Sorry, attacking on someone?? No personal here please......
-- I am not attacking on anyone. I just pointed out I wa not offended by any of your comments (since you asked). And I did not get personal, thanks.
Most production DBs would need to be in Full recovery model - that's why the organisation has log backup implemented for the database.
SLA's please...Probably you should put it in a different way. It should have been Most Production DB's recovery model depends upon the SLA's, if you dont need point-in-time recovery and the business accepts loss of data then dont have to be in full recovery model.
-- That was my point too... how do you know that in this case business needs simple recovery model? How do you know the database is 1 TB VLDB(as you mentioned before)? If the database was not deemed worth protecting with log backups, ther would not be log backups implemented in the first point.
So, you say if its a small DB to break the log chain?
-- Nopes - like I said a few lines above, the log backup regimen can be put into place once the application can be tuned to reduce its transaction log load (i.e. posting and committing smaller chunks of data in each transaction so that hourly log backups can cope with it). Meanwhile, the backup can still happen - full backups can even be backed up by differential backups.
And remember anything that is implemented by the DBA should be well documented and signed by the management. These documents are referred by your whole team which means misleading them. It really does not matter if you are in a Forum or you are at field. Your piece of advise should be the same as it reflects your work at field.
The above is from one of my posts. You document it that you have used No_LOG command and then the whole team will follow this document. Refer this which means DBA's with some less knowledge is going to implement it again.
-- Yes agreed with forum and workplace comparison. however, when you do implement an exception for a fix, system maintenance, that also should be put into a document as an appendix or related item, this can also be put into the regular DBA run-sheet. Anything which you do, when explained, cannot be misleading.
Not sure if I understood you correctly, but the simple recovery model does not give any up-to-the-minute recoverability, because logs cannot be backed up. The bulk logged mode does give log backup opportunity, but BULK INSERT, SELECT...INTO, BCP, CREATE INDEX etc. operations will be minimally logged and finally, the full recovery mode would give you full up-to-the-moment recoverability. If you are suggesting putting the DB in bulk log mode, then up-to-the-minute recoverability possibilities are gone. Because you do not know what log backupss have the minimally logged operations in them.
Agreed. Go through my post again
-- I have gone through and I have answered.
Even if log backup chains are broken, you still will have log backups since your last full backups. In the worst case your server fails when the full backup (following the breakage) is occurring, you can still rebuild the database with the last full backup and subsequent log backups.
See above again....
-- Seen and answered...
Thanks,
May 20, 2009 at 5:16 am
Okay, I am posting this again:
Problem:
A database exists in a SQL Server instance with an application regularly posting large volumes of data in it. The database log (and presumably data) file(s) are configured to grow automatically and the maximum size is set to unlimited. This causes the log to grow and fill up the entire drive where it is located. Management wants the SQL Service to be stopped every time the drive fills up. The database has log backups scheduled on it, and the log is shrunk after every log backup. However, the shrink process does not help as there are un-committed transactions in the log. The DBA has asked the application personnel to check if the size of the transactions can be made smaller, so that the log backups can take of the size. However, this is still being investigated.
Solutions / Options:
Assumptions for requirements:
a) We need to ensure SQL Server does not have to be stopped manually time and again
b) We need to ensure the log file(s) never fill up, thereby rendering the DB inaccessible
c) We need to ensure the drive space does not fill up
d) We need to ensure there is a valid and working backup regimen available – i.e. we know what files to use for restore when the need arises.
For the sake of discussion, let us also assume (since we do not have much information) that the database is part of a maintenance plan which performs a full database backup once every night and also performs log backups every hour between full backups.
1. One option will be to check the log file growth characteristics. If left to default, in SQL 2005, the log files are configured to grow by 10% of its size. So if we have a log file of say, 10 GB, every time the file needs to grow, it will allocate 1 GB in one gallop. To cater for this situation, the log (and the data) file can be configured to grow by a finite amount.
This approach does not give any concrete protection against points a, b, c above. However, it is something worth looking at and check if it serves the purpose.
2. As some have suggested – put the database in Simple Recovery mode. This will ensure the log is never growing, because with every checkpoint the log will be truncated. However, this also does not give any up-to-the-minute recoverability. So if we have a database full backup at 2:00 AM and during the whole day there is no log or differential backup, the database is effectively as safe as up to the last full backup at 2:00 AM. Even with differential backups in between (which cannot be as frequent as log backups), one cannot go back to a particular point in time.
This approach does take care of point a, b, c – however, point d is restricted.
3. As some have suggested – increasing the number of log backups. This is a valid option. However, depending on the frequency of log backups, there can be a large number of backups to be restored (and hence the downtime) when needed. So for example if we had log backups every hour and the frequency is increased up to every fifteen minutes, then potentially, we have four times as many log files to restore.
This approach may address a, b, c, d – however, even with frequent log backups, the files may still grow and fill up.
4. Another option will be to restrict the size of the log file AND put a finite file size as the growth increment (instead of a percentage). Now this will take care of point c above (drive space filling up), but it will also put the database in higher risk of its log being filled up more quickly.
To cater for this situation, we can put an alert in place that will monitor the log file usage. If the log file has grown up to, say 90% of its capacity, the alert will fire a notification to the DBA. It will then run a job as response.
This response job can do two things. Either (a) run another log backup or (b) run a log truncate.
Now the point (a) above is valid. However, this log backup file will also have to be part of the regular transaction log backup chain that is being saved through the maintenance plan (or a custom log backup job).
Also, point (b) can be valid, even though, it will break the log backup chain. How? Let us consider the following scenario:
i. The database is fully backed up at 2:00 AM
ii. The log backups happen (through maintenance plan) from 3:00 AM till, say 11:00 AM
iii. At 11:45 AM, say, the alert fires. This will trigger the response job.
iv. The response job truncates the log. However, the backup that would have happened at 12:00 noon would not contain the log entries that have been truncated.
But if you consider the scene, the DBA now knows that the log backup chain is now invalid post 11:45 because he has been notified. As a measure, the DBA can take a differential backup of the database. This will ensure all the changes since the last full backup are now in the differential backup file. The scheduled backup from 12:00 PM can go ahead.
So if we have a disaster at, say 1:45 PM, we would have the 2:00 AM full backup, 11:45 differential to put after that and the log backups of 12:00 PM and 1:00 PM.
In fact the DBA does not have to complete the differential backup himself; it can be part of the response job itself: after truncating the log, the job performs a differential backup.
And of course this alert and the response job will be a TEMPORARY measure, as long as the application architects and experts do not ensure large transactions are broken into smaller chunks.
Although 4(a) is much preferable, 4(b) is not invalid either.
If you look at the 4th option, it does protect you against log file filling up, it does protect you against a drive filling up, it does not mean you lose all your backups and have to start all over again and it certainly does not force you to restart SQL Server.
May 20, 2009 at 10:45 am
Here's some more informaiton.
1.Database is in full recovery mode and we are using database mirroring on it. We are doing nightly full backup and hourly trans backups.after each trans backup run, we have shrink transaction log job after that.
2.We need to make sure that trans. logs wont grow more than 90% of disk size.
3.If that happens then we have full authority to take sql server down.
4.Before that we want to make sure that we can recover the database at the last point before we take sql server down.
Questions:
1.If I do "Backup log with no_log then will all the transactions after last transaction will lost? Will all those transaction get rolled back from application point of view?
2.How can I disable the particular alert through script?
3.What if we just restrict the database trans log file size to 90% of disk space? Will that be an issue?
Thank you all in advance for your help regarding this issue.
May 20, 2009 at 7:26 pm
RPSql (5/20/2009)
Here's some more informaiton.1.Database is in full recovery mode and we are using database mirroring on it. We are doing nightly full backup and hourly trans backups.after each trans backup run, we have shrink transaction log job after that.
2.We need to make sure that trans. logs wont grow more than 90% of disk size.
3.If that happens then we have full authority to take sql server down.
4.Before that we want to make sure that we can recover the database at the last point before we take sql server down.
Questions:
1.If I do "Backup log with no_log then will all the transactions after last transaction will lost? Will all those transaction get rolled back from application point of view?
2.How can I disable the particular alert through script?
3.What if we just restrict the database trans log file size to 90% of disk space? Will that be an issue?
Thank you all in advance for your help regarding this issue.
Hi RPSQL,
First of all, if you are using database mirroring, you cannot put the DB in simple recovery mode and obviously you also cannot truncate the log - that means, you cannot run BACKUP LOG...WITH NO_LOG. as far as I can remember, the system will not allow you to do so as it breaks the mirroring.
In response to your first question (suppose there is no mirroring), the BACLUP LOG...WITH NO_LOG removes the inactive portion of the log from the transaction log (i.e. truncates the log) without backing it up. It frees up space in the Tlog. However, this does NOT rollback anything. You will not have the transactions in the Tlog, but the checkpoint process would have written and hardened the changes to the data files.
In response to your second question, you can make use of the system stored procedure sp_updatealert for disabling an alert, you will need to set the value of the enabled flag to zero.
In response to your third question, I believe the best option at this stage is to restrict the log file to a finite size. Please see my message before. As I said, this will ensure the disk does not get filled up - however, you will have to monitor the log file continuously. In this case, your defined alert will do the job. In response to your alert, a job can run a BACKUP LOG command (without the NO_LOG part).
Now in response to the point (4) in your message - "Before that we want to make sure that we can recover the database at the last point before we take sql server down", I can also say that if you have an alert that checks log file usage and backs up the log in response, that log backup will be the last in your chain of Tog backups that you will need for your recovery.
Hope this helps.
May 20, 2009 at 9:08 pm
I would also look at increasing the frequency of your transaction log backups from every hour to every 15 minutes. I would also consider adding an alert that would trigger a transaction log backup when the transaction log hits 75% full. If this triggers and you still eventually hit 90% full on your transaction log, you know that there is a definite problem somewhere with your application and open transactions that can't be cleared.
May 20, 2009 at 10:34 pm
Lynn Pettis (5/20/2009)
If this triggers and you still eventually hit 90% full on your transaction log, you know that there is a definite problem somewhere with your application and open transactions that can't be cleared.
I agree. one way to check for open transaction is to run the DBCC OPENTRAN command. It will give you the SPID for the oldest open transaction. You may want to then further look into it using DBCC INPUTBUFFER command.
May 21, 2009 at 6:25 am
RPSql (5/20/2009)
Here's some more informaiton.1.Database is in full recovery mode and we are using database mirroring on it. We are doing nightly full backup and hourly trans backups.after each trans backup run, we have shrink transaction log job after that.
2.We need to make sure that trans. logs wont grow more than 90% of disk size.
3.If that happens then we have full authority to take sql server down.
4.Before that we want to make sure that we can recover the database at the last point before we take sql server down.
Questions:
1.If I do "Backup log with no_log then will all the transactions after last transaction will lost? Will all those transaction get rolled back from application point of view?
2.How can I disable the particular alert through script?
3.What if we just restrict the database trans log file size to 90% of disk space? Will that be an issue?
Thank you all in advance for your help regarding this issue.
BACKUP WITH NO_LOG- please do not pratice using this command. and also as advised it has been deprecated in SQL 2008 by MS. As you can see from my posts it will wreak a havoc against your server. You should never even think of breaking the log chain and find a solution which could bring back it again and in your case it will definitely break the DB mirroring. No, it will not roll back any transactions, you will lose all the data.
Would you not consider having safe backups?
you can create an alert to notify when the tlog has exceeded your desired space and then either you can shrink the log file( not advisable) or create more disk space.
If you manage to maintain your tlog effitiently why would you even think of a situation to bring your sql server down? Take frequent log backups and monitor any bulk operations running against your server and in your code commit frequently.
In any situation you would not want to consider NO_LOG command. Losing backups is losing your business.
Thanks,
May 21, 2009 at 6:30 am
Sadequl Hussain (5/20/2009)
Okay, I am posting this again:Problem:
A database exists in a SQL Server instance with an application regularly posting large volumes of data in it. The database log (and presumably data) file(s) are configured to grow automatically and the maximum size is set to unlimited. This causes the log to grow and fill up the entire drive where it is located. Management wants the SQL Service to be stopped every time the drive fills up. The database has log backups scheduled on it, and the log is shrunk after every log backup. However, the shrink process does not help as there are un-committed transactions in the log. The DBA has asked the application personnel to check if the size of the transactions can be made smaller, so that the log backups can take of the size. However, this is still being investigated.
Solutions / Options:
Assumptions for requirements:
a) We need to ensure SQL Server does not have to be stopped manually time and again
b) We need to ensure the log file(s) never fill up, thereby rendering the DB inaccessible
c) We need to ensure the drive space does not fill up
d) We need to ensure there is a valid and working backup regimen available – i.e. we know what files to use for restore when the need arises.
For the sake of discussion, let us also assume (since we do not have much information) that the database is part of a maintenance plan which performs a full database backup once every night and also performs log backups every hour between full backups.
1. One option will be to check the log file growth characteristics. If left to default, in SQL 2005, the log files are configured to grow by 10% of its size. So if we have a log file of say, 10 GB, every time the file needs to grow, it will allocate 1 GB in one gallop. To cater for this situation, the log (and the data) file can be configured to grow by a finite amount.
This approach does not give any concrete protection against points a, b, c above. However, it is something worth looking at and check if it serves the purpose.
2. As some have suggested – put the database in Simple Recovery mode. This will ensure the log is never growing, because with every checkpoint the log will be truncated. However, this also does not give any up-to-the-minute recoverability. So if we have a database full backup at 2:00 AM and during the whole day there is no log or differential backup, the database is effectively as safe as up to the last full backup at 2:00 AM. Even with differential backups in between (which cannot be as frequent as log backups), one cannot go back to a particular point in time.
This approach does take care of point a, b, c – however, point d is restricted.
3. As some have suggested – increasing the number of log backups. This is a valid option. However, depending on the frequency of log backups, there can be a large number of backups to be restored (and hence the downtime) when needed. So for example if we had log backups every hour and the frequency is increased up to every fifteen minutes, then potentially, we have four times as many log files to restore.
This approach may address a, b, c, d – however, even with frequent log backups, the files may still grow and fill up.
4. Another option will be to restrict the size of the log file AND put a finite file size as the growth increment (instead of a percentage). Now this will take care of point c above (drive space filling up), but it will also put the database in higher risk of its log being filled up more quickly.
To cater for this situation, we can put an alert in place that will monitor the log file usage. If the log file has grown up to, say 90% of its capacity, the alert will fire a notification to the DBA. It will then run a job as response.
This response job can do two things. Either (a) run another log backup or (b) run a log truncate.
Now the point (a) above is valid. However, this log backup file will also have to be part of the regular transaction log backup chain that is being saved through the maintenance plan (or a custom log backup job).
Also, point (b) can be valid, even though, it will break the log backup chain. How? Let us consider the following scenario:
i. The database is fully backed up at 2:00 AM
ii. The log backups happen (through maintenance plan) from 3:00 AM till, say 11:00 AM
iii. At 11:45 AM, say, the alert fires. This will trigger the response job.
iv. The response job truncates the log. However, the backup that would have happened at 12:00 noon would not contain the log entries that have been truncated.
But if you consider the scene, the DBA now knows that the log backup chain is now invalid post 11:45 because he has been notified. As a measure, the DBA can take a differential backup of the database. This will ensure all the changes since the last full backup are now in the differential backup file. The scheduled backup from 12:00 PM can go ahead.
So if we have a disaster at, say 1:45 PM, we would have the 2:00 AM full backup, 11:45 differential to put after that and the log backups of 12:00 PM and 1:00 PM.
In fact the DBA does not have to complete the differential backup himself; it can be part of the response job itself: after truncating the log, the job performs a differential backup.
And of course this alert and the response job will be a TEMPORARY measure, as long as the application architects and experts do not ensure large transactions are broken into smaller chunks.
Although 4(a) is much preferable, 4(b) is not invalid either.
Are you sure you can take a differential backup once the tlog has been truncated- (which means you have broken the log chain)- once truncated only possible backup is the Full database backup.
If you look at the 4th option, it does protect you against log file filling up, it does protect you against a drive filling up, it does not mean you lose all your backups and have to start all over again and it certainly does not force you to restart SQL Server.
There aare better ways to protect server against filling up rather than advising to break the log chain so that the drive doesnt fill up.
May 21, 2009 at 6:43 am
How Can I stop SQL Server Service ...?
If it is cluster you need run cmd:
cluster . res "SQL service name" /off
May 21, 2009 at 7:35 am
Krishna Potlakayala (5/21/2009)
Are you sure you can take a differential backup once the tlog has been truncated- (which means you have broken the log chain)- once truncated only possible backup is the Full database backup.
Differential backups are possible after a log truncation.
Books Online (November 2008) Topic="BACKUP (Transact-SQL)"
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure.
May 21, 2009 at 11:12 am
Scott Coleman (5/21/2009)
Krishna Potlakayala (5/21/2009)
Are you sure you can take a differential backup once the tlog has been truncated- (which means you have broken the log chain)- once truncated only possible backup is the Full database backup.
Differential backups are possible after a log truncation.
Books Online (November 2008) Topic="BACKUP (Transact-SQL)"
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure.
Oopss..what a blunder..what am i typing,...my bad, thanks for correcting me.....
May 21, 2009 at 3:31 pm
Sadequl Hussain (5/20/2009)
Okay, I am posting this again:Problem:
A database exists in a SQL Server instance with an application regularly posting large volumes of data in it. The database log (and presumably data) file(s) are configured to grow automatically and the maximum size is set to unlimited. This causes the log to grow and fill up the entire drive where it is located. Management wants the SQL Service to be stopped every time the drive fills up. The database has log backups scheduled on it, and the log is shrunk after every log backup. However, the shrink process does not help as there are un-committed transactions in the log. The DBA has asked the application personnel to check if the size of the transactions can be made smaller, so that the log backups can take of the size. However, this is still being investigated.
Solutions / Options:
Assumptions for requirements:
a) We need to ensure SQL Server does not have to be stopped manually time and again
b) We need to ensure the log file(s) never fill up, thereby rendering the DB inaccessible
c) We need to ensure the drive space does not fill up
d) We need to ensure there is a valid and working backup regimen available – i.e. we know what files to use for restore when the need arises.
For the sake of discussion, let us also assume (since we do not have much information) that the database is part of a maintenance plan which performs a full database backup once every night and also performs log backups every hour between full backups.
1. One option will be to check the log file growth characteristics. If left to default, in SQL 2005, the log files are configured to grow by 10% of its size. So if we have a log file of say, 10 GB, every time the file needs to grow, it will allocate 1 GB in one gallop. To cater for this situation, the log (and the data) file can be configured to grow by a finite amount.
This approach does not give any concrete protection against points a, b, c above. However, it is something worth looking at and check if it serves the purpose.
2. As some have suggested – put the database in Simple Recovery mode. This will ensure the log is never growing, because with every checkpoint the log will be truncated. However, this also does not give any up-to-the-minute recoverability. So if we have a database full backup at 2:00 AM and during the whole day there is no log or differential backup, the database is effectively as safe as up to the last full backup at 2:00 AM. Even with differential backups in between (which cannot be as frequent as log backups), one cannot go back to a particular point in time.
This approach does take care of point a, b, c – however, point d is restricted.
3. As some have suggested – increasing the number of log backups. This is a valid option. However, depending on the frequency of log backups, there can be a large number of backups to be restored (and hence the downtime) when needed. So for example if we had log backups every hour and the frequency is increased up to every fifteen minutes, then potentially, we have four times as many log files to restore.
This approach may address a, b, c, d – however, even with frequent log backups, the files may still grow and fill up.
4. Another option will be to restrict the size of the log file AND put a finite file size as the growth increment (instead of a percentage). Now this will take care of point c above (drive space filling up), but it will also put the database in higher risk of its log being filled up more quickly.
To cater for this situation, we can put an alert in place that will monitor the log file usage. If the log file has grown up to, say 90% of its capacity, the alert will fire a notification to the DBA. It will then run a job as response.
This response job can do two things. Either (a) run another log backup or (b) run a log truncate.
Now the point (a) above is valid. However, this log backup file will also have to be part of the regular transaction log backup chain that is being saved through the maintenance plan (or a custom log backup job).
Also, point (b) can be valid, even though, it will break the log backup chain. How? Let us consider the following scenario:
i. The database is fully backed up at 2:00 AM
ii. The log backups happen (through maintenance plan) from 3:00 AM till, say 11:00 AM
iii. At 11:45 AM, say, the alert fires. This will trigger the response job.
iv. The response job truncates the log. However, the backup that would have happened at 12:00 noon would not contain the log entries that have been truncated.
But if you consider the scene, the DBA now knows that the log backup chain is now invalid post 11:45 because he has been notified. As a measure, the DBA can take a differential backup of the database. This will ensure all the changes since the last full backup are now in the differential backup file. The scheduled backup from 12:00 PM can go ahead.
So if we have a disaster at, say 1:45 PM, we would have the 2:00 AM full backup, 11:45 differential to put after that and the log backups of 12:00 PM and 1:00 PM.
In fact the DBA does not have to complete the differential backup himself; it can be part of the response job itself: after truncating the log, the job performs a differential backup.
And of course this alert and the response job will be a TEMPORARY measure, as long as the application architects and experts do not ensure large transactions are broken into smaller chunks.
Although 4(a) is much preferable, 4(b) is not invalid either.
If you look at the 4th option, it does protect you against log file filling up, it does protect you against a drive filling up, it does not mean you lose all your backups and have to start all over again and it certainly does not force you to restart SQL Server.
Agreed. You are talking about a perfect world but unfortunately it's not. And also your problem and solution describes to cater your solution.
Let's say for this sake of discussion the same database has got corrupted at 11:30 am before the tlog has been truncated. Now according to your approach once the tlog is full it gets truncated at 11:45 without any prior notice to the DBA about the corruption. So, eventually you break the log chain and schedule it to your solution.
What are the options the DBA has got now? Only a clean Full backup at 2:00 AM? and according to your solution even if you start taking differential backup would be of no use for the business as the backup either would not run or it would with a corrupted differential backup file.
I would not even think of breaking the log chain a valid solution or as an option. Even if I did, In would not want to advise this as a valid solution to the folks out there and would share this with others. You might be very sound in using it and you might have good intentions to advise them or you might be aware of the situations when to use them and when not to, but how much you can guarantee about others misusing it? the "others" being "the involuntary DBA's" as described by Brad Mcghee.
As you can see in the forums most of the OP's are either developers/newbies into the field who does not have clearer idea about the basic function of Tlog. They come here to ask us the advise and we always try to give an optimal solution rather than a shortcut. That's why I am still stressing on the point to take frequent log backups. In this topic itself if you look at the OP asking about using the NO_LOG while he has DB mirroring set up. So would you still prefer to advise to manually truncate the log? That's a risky business.
May 21, 2009 at 6:26 pm
Krishna Potlakayala (5/21/2009)
Sadequl Hussain (5/20/2009)
Okay, I am posting this again:Problem:
A database exists in a SQL Server instance with an application regularly posting large volumes of data in it. The database log (and presumably data) file(s) are configured to grow automatically and the maximum size is set to unlimited. This causes the log to grow and fill up the entire drive where it is located. Management wants the SQL Service to be stopped every time the drive fills up. The database has log backups scheduled on it, and the log is shrunk after every log backup. However, the shrink process does not help as there are un-committed transactions in the log. The DBA has asked the application personnel to check if the size of the transactions can be made smaller, so that the log backups can take of the size. However, this is still being investigated.
Solutions / Options:
Assumptions for requirements:
a) We need to ensure SQL Server does not have to be stopped manually time and again
b) We need to ensure the log file(s) never fill up, thereby rendering the DB inaccessible
c) We need to ensure the drive space does not fill up
d) We need to ensure there is a valid and working backup regimen available – i.e. we know what files to use for restore when the need arises.
For the sake of discussion, let us also assume (since we do not have much information) that the database is part of a maintenance plan which performs a full database backup once every night and also performs log backups every hour between full backups.
1. One option will be to check the log file growth characteristics. If left to default, in SQL 2005, the log files are configured to grow by 10% of its size. So if we have a log file of say, 10 GB, every time the file needs to grow, it will allocate 1 GB in one gallop. To cater for this situation, the log (and the data) file can be configured to grow by a finite amount.
This approach does not give any concrete protection against points a, b, c above. However, it is something worth looking at and check if it serves the purpose.
2. As some have suggested – put the database in Simple Recovery mode. This will ensure the log is never growing, because with every checkpoint the log will be truncated. However, this also does not give any up-to-the-minute recoverability. So if we have a database full backup at 2:00 AM and during the whole day there is no log or differential backup, the database is effectively as safe as up to the last full backup at 2:00 AM. Even with differential backups in between (which cannot be as frequent as log backups), one cannot go back to a particular point in time.
This approach does take care of point a, b, c – however, point d is restricted.
3. As some have suggested – increasing the number of log backups. This is a valid option. However, depending on the frequency of log backups, there can be a large number of backups to be restored (and hence the downtime) when needed. So for example if we had log backups every hour and the frequency is increased up to every fifteen minutes, then potentially, we have four times as many log files to restore.
This approach may address a, b, c, d – however, even with frequent log backups, the files may still grow and fill up.
4. Another option will be to restrict the size of the log file AND put a finite file size as the growth increment (instead of a percentage). Now this will take care of point c above (drive space filling up), but it will also put the database in higher risk of its log being filled up more quickly.
To cater for this situation, we can put an alert in place that will monitor the log file usage. If the log file has grown up to, say 90% of its capacity, the alert will fire a notification to the DBA. It will then run a job as response.
This response job can do two things. Either (a) run another log backup or (b) run a log truncate.
Now the point (a) above is valid. However, this log backup file will also have to be part of the regular transaction log backup chain that is being saved through the maintenance plan (or a custom log backup job).
Also, point (b) can be valid, even though, it will break the log backup chain. How? Let us consider the following scenario:
i. The database is fully backed up at 2:00 AM
ii. The log backups happen (through maintenance plan) from 3:00 AM till, say 11:00 AM
iii. At 11:45 AM, say, the alert fires. This will trigger the response job.
iv. The response job truncates the log. However, the backup that would have happened at 12:00 noon would not contain the log entries that have been truncated.
But if you consider the scene, the DBA now knows that the log backup chain is now invalid post 11:45 because he has been notified. As a measure, the DBA can take a differential backup of the database. This will ensure all the changes since the last full backup are now in the differential backup file. The scheduled backup from 12:00 PM can go ahead.
So if we have a disaster at, say 1:45 PM, we would have the 2:00 AM full backup, 11:45 differential to put after that and the log backups of 12:00 PM and 1:00 PM.
In fact the DBA does not have to complete the differential backup himself; it can be part of the response job itself: after truncating the log, the job performs a differential backup.
And of course this alert and the response job will be a TEMPORARY measure, as long as the application architects and experts do not ensure large transactions are broken into smaller chunks.
Although 4(a) is much preferable, 4(b) is not invalid either.
If you look at the 4th option, it does protect you against log file filling up, it does protect you against a drive filling up, it does not mean you lose all your backups and have to start all over again and it certainly does not force you to restart SQL Server.
Agreed. You are talking about a perfect world but unfortunately it's not. And also your problem and solution describes to cater your solution.
Let's say for this sake of discussion the same database has got corrupted at 11:30 am before the tlog has been truncated. Now according to your approach once the tlog is full it gets truncated at 11:45 without any prior notice to the DBA about the corruption. So, eventually you break the log chain and schedule it to your solution.
What are the options the DBA has got now? Only a clean Full backup at 2:00 AM? and according to your solution even if you start taking differential backup would be of no use for the business as the backup either would not run or it would with a corrupted differential backup file.
I would not even think of breaking the log chain a valid solution or as an option. Even if I did, In would not want to advise this as a valid solution to the folks out there and would share this with others. You might be very sound in using it and you might have good intentions to advise them or you might be aware of the situations when to use them and when not to, but how much you can guarantee about others misusing it? the "others" being "the involuntary DBA's" as described by Brad Mcghee.
As you can see in the forums most of the OP's are either developers/newbies into the field who does not have clearer idea about the basic function of Tlog. They come here to ask us the advise and we always try to give an optimal solution rather than a shortcut. That's why I am still stressing on the point to take frequent log backups. In this topic itself if you look at the OP asking about using the NO_LOG while he has DB mirroring set up. So would you still prefer to advise to manually truncate the log? That's a risky business.
I am not talking about a perfect world situation - I described a typical situation of full backup nightly and hourly transaction log backups - which the OP later described to be the case in his organisation.
If I have understood you correctly - the DB gets corrupted, say at 11:30 AM, and the truncation (my second approach) happens at 11:45 AM. The trunctation will clear only changes that are hardened to data files. This hardened changes can include the corrupted data. Now say, you realise about the corruption at 12:45 PM.
If you know roughly what time the corruption would have occured, you will have an idea about what log files to include. You will restore the full backup file, then all the logs up-to 11:00 AM. Because you would know that you do not have any log backups after that which can take you, up to, say 11:29 AM, you will only lose 30 mins of data. You are not forced to go back to 2:00 AM, nor are you forced to restor the differential backup that would contain the corrupted data.
Now obviously we would not want to lose up to 11:29 AM worth of data. If you look at my e-mail, I also suggested running a BACKUP LOG - WITHOUT the truncation. I just said the second approach was also valid.
Don't get me wrong - I am not an advoccate of log truncation and log backup chain breaking and do not preach practising this. If you saw my later posts, you would have seen that I asked NOT to try log truncation when the OP said the DB was in mirroring session. I would say the same for DBs particpating in log shipping. Because in both the cases, re-setting up LS or Mirroring would be required and that would be a time consuming process.
Also, instead of backing up log to clear space - I would try to find exactly what transaction or SPID was keeping active records in the log. Someone suggested about open transaction - you would have seen my response to that.
As for people who are "accidental DBAs" like Brad, unfortunately - the situation still persists to this day - you still learn from your mistakes and pitfalls. When you are learning, your primary source of knowledge for SQL Server should be Books Online and other printed books. Forums like this are useful only when you do not find answers in those places. However, when novices come to these boards, they also probably do not read just one post - they also see what others are saying. People would not usually see one thing and go start prescribing it to others, unless they themselves tried the solution first.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply