May 18, 2009 at 11:38 am
Jeremy Brown (5/18/2009)
Agree completely David. Just making the point for the OP, looking out for him too.I get asked to do stupid things by clients / managers all the time who refuse to listen to anything else. So for his benefit I encourage him to use the methods others have mentioned here to find and resolve his issue, but don't push your luck. And if you work in that kind of environment... get out... fast, but on your own terms. 😉
I believe as a DBA that's your first job to let the know world that they are welcoming problems which could be a utter disaster in the future. Clients might request you with some stupid ideas but its your responsibility to educate them what is right and what is wrong that's what makes you a recognizable DBA rather than just satisfying the customers in what they need.
If I had the same situation I would educate them, advise them and show them with sample examples was what could the future of the company look like from a simple mistake to a disaster.
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.
May 18, 2009 at 12:00 pm
If you are having trouble with the transaction log file getting too large, set the transaction log backups to run much more often, instead of having one hour between transaction log backups.
We usually run backups every 15 minutes, 24x7, but sometimes run them as often as every 5 minutes. This will not completely solve your problem if you have very large transactions, but it can help.
Another thing you can try is creating additional log files on other drives that have more open space.
May 18, 2009 at 12:00 pm
Jeremy Brown (5/18/2009)
Not to belabor the point but I think the OP stated that the requirement is to stop sql server by his management. As much as I agree with tracing down the root causes for the issue and getting tlog management under control, lets not get this guy fired for not doing what they're asking him to do. Even if its stupid.
What does management know about SQL Server to be honest? Grade it and let me know your views. How much knowledge they have got about Tlog? Unless there is a guy in the management with a DBA experience. show them with the strong evidences and references and show them the trends with graphs and spreadsheets. Do you think they would still get fired? Well if he did then the DBA is wasting his time working for the company which does not welcome ideas. As always DBA's role is to safe guard the business.
May 18, 2009 at 12:04 pm
I'd argue the DBA's job is to do what his boss wants, as long as it is not illegal. If someone is paying you to do a job, you should do it. The "it" being what they want you to do.
You can advise them, give them the risks, but ultimately it isn't your company or your decision if you don't own the company or are the CEO/President. If they insist on running with no backups, you do it.
Or find another job.
Don't take it personally if they want to do stupid things. Deal with it as a professional, and look for another job if you feel you must.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 18, 2009 at 1:19 pm
I'd agree that you have to do what management says but what if a disaster happens then who would be blamed at the end, it's the DBA? Would you not try your best to explain what is going to be the future of their business for taking those decisions? I want to justify the knowledge and skills that I have gained for sometime, I would definitely don't want to follow If it's going to harm the future business, personally it's not me (God should save me!!):-) and apparently I do not have any problems with my management whenever I take time to struggle and do corrections to what they have told. I would better leave the company if they don't welcome my ideas and if they don't respect my knowledge. The best practices that have been proved by some bright minds in the field would not be useful if you do not follow them. I don't know whether it would create me any problems in my future career but till now it ran smoothly.
If they insist on running with no backups- Yes, if the SLA's says they are ready to lose the data but if the SLA says loss of data should be very minimal then I would not go for such a plan, I would definitely ask them add an extra statement in the SLA's.
This is what I feel and I do personally by the way and have been following the same.
I should apologize to the OP and all others for going out of track from original question.
May 18, 2009 at 3:08 pm
My fundamental belief is a DBA's job comes down to three thing.
1. Ensure data availability
2. Ensure data recoverability
3. Ensure data security.
If management won't support you in these efforts, you need a personal exit strategy.
May 18, 2009 at 7:40 pm
Krishna Potlakayala (5/18/2009)
Sadequl Hussain (5/16/2009)
Instead of stopping SQL Server, there are other options you can take.Before the next run of the data run (that increases the log file to fill up the disk), ensure you have taken a log backup. Then TRUNCATE the log. The command for SQL Server 2005 would be
BACKUP LOG WITH NO_LOG
For SQL 2000, it would be BACKUP LOG WITH TRUNCATE_ONLY
No these are not options at all. that's a wrong piece of advise. TRUNCATE_ONLY will break the backup chain and its a dissater if you are aiming for 5 9's (99.999%). And also it has been deprecated in SQL 2008.
I am afraid these ARE options. At least better than stopping SQL Server. And I did mention about truncation breaking log chain - read before typing. The poster did not say if it was a SQL 2008 system. In any case, the equivalent command in 2008 can be used.
There are better options what is your backup schedule for Tlogs? do you have frequent commits in your code? and also did you think of setting the recovery model to bulk-logged while you have bulk operations running against your table?
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 will break the log backup chain, but you can recreate / reschedule it. Once the log is cleared, either shrink it to a value you want, the command for that would be:
It's not that easy to build it if the backup chain is broken, is it? And also re think before you shrink the log file, it needs space to grow, if it needs it it will grow again.....frequent shrinking and growing the log file will cause fragmentation....
Again, it is ONE of the options - either shrink the log file to a size or better, keep it with a fixed size. 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.
DBCC SHRINKFILE('', )
Alternately, you can keep the same value for the file size - say, 12000 MB. Once the log file is shrunk (or if you decide to keep the file size same) restrict the max size for .LDF file up to that limit. You can do that from the database properties.
OMG! restrict the log file to grow?? Why???
Yes 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.
This approach will ensure the log will never fill up the disk drive, because the log will not grow beyond the limit.
True but with business downtime.. are you ready on that?
Are you ready for the business downtime that stopping a SQL Server would cause?
Now, if user transaction fills up the log, the database (not the whole server) will be inaccessible. The only way out of that will be to clear the log. So to cater for this situation, you can do another thing.
If you are using SQL 2005, create a database alert. For the alert
a) Choose the type of "SQL Server Performance Condition Alert".
b) Choose Object as SQL Server Instance:Database
c) Choose "Percent Log Used" as counter
d) Choose the database name as instance
e) Choose "Alert if counter becomes equal to" and specify a percent value (say 90).
So we want to fire an alert if the log is used 90%.
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.
Very wrong !!!!!!!!!
I am afraid not - look at SQL 2005 and try to implement it.
You should rather think in a constructive way and look for possibilities to manage your tlog efficiently rather than going for shortcuts which will cause a huge problem in the future.
There are different ways to approach a problem - the approach I mentioned, was not "destructive" I am afraid.
What is your recovery model set to? If it is full do you really need it. If it is not then what is your backup schedule? how frequent do you take your tlog backups? If there are any bulk-operations did you consider changingthe recovery model to bulk-logged?
Most production DBs would need to be in Full recovery model - that's why the organisation has log backup implemented for the database. Changing the recovery model would probably depend upon management decision - I am sure they would go against it if told it will not give up-to-the-minute recoverability. Also, bul logged operations minimally logs bulk operations - the Tlog backups will be of no use if they want to rollback any changes.
May 19, 2009 at 4:31 am
This has become an interesting secondary debate worthy of a separate discussion thread.
Should one follow directives from above, even when those directives at best contradict best practice, and at worst endanger data and or system availability?
May 19, 2009 at 7:23 am
In a situation where management requires you to handle huge transactions, but refuses to buy the hardware to deal with it, even in the face of log truncations and server shutdowns, I might consider moving to MySQL and using a non-logged database engine. That's got to be more stable than putting SHUTDOWN commands in alert jobs.
But first I would follow some of the excellent advice posted already. Consider increasing the log backup frequency. Can you break the large transactions into smaller chunks to limit t-log growth? Are there open transactions, or some other reason for the active log to keep growing?
There may be other ways to get the job done without filling the transaction log. For example, an alternative to updating a large table is to use BCP OUT with a query to write the new table values to a flat file, truncate the table, then reload it with BCP IN in simple or bulk-logged recovery mode. Read Books Online about the conditions you must satisfy so this will run quickly as a non-logged operation. The drawback is a slight reduction in concurrency as the table will be locked the whole time, but those other connections would be mildly inconvenienced by a shutdown anyway.
May 19, 2009 at 10:00 am
Here's a great link on what not to do with trans log backups.
To summarize it:
1. Don't break the backup chain with NO_LOG/TRUNCATE_ONLY.
2. If you really must, immediately do a full backup afterwards and pray nothing goes wrong with the system before the backup is finished.
3. Increase the frequency of trans. log backups. If it is every hour, make it every half hour. If it is every half hour, make it every 15 minutes, whatever it takes to backup the logs in manageable sizes.
You may also want to check how long you're keeping your trans log backups (and full backups if on same drive) and think about reducing the amount of time you keep them. Are you saving 72 hours worth of trans log files, maybe you only need 24, plus 2 full backups. If you have a third party application that archives your backups remotely to tape (in our case the program is Netbackup), this could make your choice easier. Also, if you're not using SQL 2008, you may consider in investing in a third party SQL backup package that is much faster than native SQL Backup, but more importantly, can be set to compress the backup.
And I know it sounds silly, but dig around in the drive you're backing up to. You may have a forgotten backup you don't need anymore that was made just before an upgrade. Sometimes it's possible that SQL Server is set up right, but other issues in the Windows Server may be having an impact. There may be setup files that were temporarily copied there. Ideally, the drive should be only for backups, but things happen for whatever reason, and other files creep up there.
As for requests being made beyond your control, hopefully you report to a manager who will be an advocate for you in talking to other managers and teams. You'll have to be diplomatic, say you understand where they are coming from, you know, stroke their egos and make them feel good. But eventually let them know you would like to come up with a mutual solution that will result in a stable application and database. Follow their requests to restart the server in the beginning, but cover yourself and try not to break the backup chain at the very least. Hopefully they will be persuaded to work with you to find something you could both live with.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
May 19, 2009 at 12:09 pm
NET START MSSQLSERVER
NET START MSSQLSERVER -m // Single user mode
NET STOP MSSQLSERVER
May 19, 2009 at 4:03 pm
Sadequl,
I am afraid these ARE options. At least better than stopping SQL Server.
On what basis you say that?
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.
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!!!!
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?
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.
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?
Yes 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.
I am afraid not - look at SQL 2005 and try to implement it.
Sorry, I did not get that?
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.
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.
Changing the recovery model would probably depend upon management decision - I am sure they would go against it if told it will not give up-to-the-minute recoverability. Also, bul logged operations minimally logs bulk operations - the Tlog backups will be of no use if they want to rollback any changes.
Yes for only bulk operations???
May 19, 2009 at 4:10 pm
Andy Hogg (5/19/2009)
Should one follow directives from above, even when those directives at best contradict best practice, and at worst endanger data and or system availability?
Nope definitely not. Here Top posters and all others try to give the best advise available in the field unlike other forums- that is what makes this forum so special. 🙂 and I love this forum for that. They take responsibility and get more information needed about the business and advise accordingly.
May 19, 2009 at 6:27 pm
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.
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.
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.
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.
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.
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.
Yes 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.
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.
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.
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.
Changing the recovery model would probably depend upon management decision - I am sure they would go against it if told it will not give up-to-the-minute recoverability. Also, bul logged operations minimally logs bulk operations - the Tlog backups will be of no use if they want to rollback any changes.
Yes for only bulk operations???
-- 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.
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.
May 20, 2009 at 12:09 am
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.
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.
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
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.
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??
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......
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?
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.
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
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....
Thanks,
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply