Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Help with log file?? 1
- Thread starter ifrydr
- Start date
This is usually an indication that your sql server is low on HD space primarily on the drive or volume housing the transaction logs.
The trans log is probably trying to grow itself and does not have enough room to do so.
Using enterprise manager on the server you should be able to backup the transaction log of your active database to another volume on the server. This will truncate the log file. If you do not have enough space there is a white paper on macolas site on other methods of truncating the log. I do not have them in front of me at the moment.
I would suggest that you look at the maintenance plans enacted for your db and insure they are
1. Actually in place and running.
2. Not keeping the backups too long.
Some people tend to rely on BU exec or other backup software to maintain the truncation after backup. I personally do not like this as most of the time it does not work anyway.
If you have problems with the log after truncating it or backing it up, or you are just not SQL enabled at your company (meaning you have someone on hand that is very comfortable and knowledgeable with SQL server), I would further suggest that you contact you VAR to help you with this.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
The trans log is probably trying to grow itself and does not have enough room to do so.
Using enterprise manager on the server you should be able to backup the transaction log of your active database to another volume on the server. This will truncate the log file. If you do not have enough space there is a white paper on macolas site on other methods of truncating the log. I do not have them in front of me at the moment.
I would suggest that you look at the maintenance plans enacted for your db and insure they are
1. Actually in place and running.
2. Not keeping the backups too long.
Some people tend to rely on BU exec or other backup software to maintain the truncation after backup. I personally do not like this as most of the time it does not work anyway.
If you have problems with the log after truncating it or backing it up, or you are just not SQL enabled at your company (meaning you have someone on hand that is very comfortable and knowledgeable with SQL server), I would further suggest that you contact you VAR to help you with this.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
Also you may want to check out this thread
The sql for truncating the log without backing it up are in it and can be run from the query analyzer.
WARNING: Doing this without a good SQL Admin knowledge is not advisable.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
The sql for truncating the log without backing it up are in it and can be run from the query analyzer.
WARNING: Doing this without a good SQL Admin knowledge is not advisable.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
-
1
- #4
crystalreporting
Instructor
I've seen this topic come up a lot, and I'm wondering what people's opinions are on using the 'simple recovery' model. My understanding of the log file is that it allows for the recovery of files to a certain point in time. Given that there is no practical way to use this feature with Macola (that I know of), what benefit does the log file provide?
Peter Shirley
Peter Shirley
I disagree, There is a practical application for this functionality with Macola. I have used it a couple of times myself.
If a simple recovery model is chosen you can not do incremental backups in a maintenance plan of the db. Here is a simple implementation of the log file and point in time recovery.
Maintenance plans in place
1. Daily backup to a HD or volume other than the one natively housing the SQL db's without any optimization, index checking etc.
2. Every 1 or 2 hours during business hours backup the trans log to the HD.
3. On a non-business day or during non-business hours (Sunday etc) do another plan that just entails the optimization index checking etc.
Step 2 accomplishes the Point in time backup ability.
If you were to corrupt a db, do some closing or posting that corrupted, did not finish or whatever you could effectively do the following.
1 Restore the backup from the night before whether from tape or the backup written to the other volume.
2 Restore all the trans logs from the morning up to the one just prior to the occurance of the problem.
In the backup plans listed above you would at most loose 1 hr and 59 minutes of work. Without it your only option is to recover the backup from tape (if the customer has it and has verified that it works) and have to recreate all the work for the entire day. Imagine the failure occuring at 5:00 p.m. on Friday. With the maintenance plan in place starting trans logs at 9, 11, 1, 3, and 5 you would be able to recover up to 3 o'clock and only have to recreate 2 hours woth the work. If you did the plan to do trans logs every 1 hour then you would only loose 1 hour of work. The time frame should not be much lower than that depending on the speed, ram, and disk space available to the server.
Had one client backing up trans logs every 30 minutes with no noticable speed problems to the end users. He has had to use this on at least 4 occasions that I know of. Instead of loosing the whole day the lost 30 minutes of work.
In essence you are correct. Macola has no built in functionality to utilize this SQL feature. Then again Macola does not use man SQL features anyway. This functionality is implemented and utilized by the SQL Admin as part of their overall disaster recovery model.
I would consider the loss or corruption of macola data to be a disaster worthy of a few minutes work up front to allow for quick and easy recovery.
Andy
Andy Baldwin
"Testing is the most overlooked programming language on the books!
If a simple recovery model is chosen you can not do incremental backups in a maintenance plan of the db. Here is a simple implementation of the log file and point in time recovery.
Maintenance plans in place
1. Daily backup to a HD or volume other than the one natively housing the SQL db's without any optimization, index checking etc.
2. Every 1 or 2 hours during business hours backup the trans log to the HD.
3. On a non-business day or during non-business hours (Sunday etc) do another plan that just entails the optimization index checking etc.
Step 2 accomplishes the Point in time backup ability.
If you were to corrupt a db, do some closing or posting that corrupted, did not finish or whatever you could effectively do the following.
1 Restore the backup from the night before whether from tape or the backup written to the other volume.
2 Restore all the trans logs from the morning up to the one just prior to the occurance of the problem.
In the backup plans listed above you would at most loose 1 hr and 59 minutes of work. Without it your only option is to recover the backup from tape (if the customer has it and has verified that it works) and have to recreate all the work for the entire day. Imagine the failure occuring at 5:00 p.m. on Friday. With the maintenance plan in place starting trans logs at 9, 11, 1, 3, and 5 you would be able to recover up to 3 o'clock and only have to recreate 2 hours woth the work. If you did the plan to do trans logs every 1 hour then you would only loose 1 hour of work. The time frame should not be much lower than that depending on the speed, ram, and disk space available to the server.
Had one client backing up trans logs every 30 minutes with no noticable speed problems to the end users. He has had to use this on at least 4 occasions that I know of. Instead of loosing the whole day the lost 30 minutes of work.
In essence you are correct. Macola has no built in functionality to utilize this SQL feature. Then again Macola does not use man SQL features anyway. This functionality is implemented and utilized by the SQL Admin as part of their overall disaster recovery model.
I would consider the loss or corruption of macola data to be a disaster worthy of a few minutes work up front to allow for quick and easy recovery.
Andy
Andy Baldwin
"Testing is the most overlooked programming language on the books!
The tempdb should be set to simple recovery. It's not a Macola database, it's a system database, and it acts as a temporary repository for transactions performed by both the system and user databases.
If set to simple, it won't keep a transaction log file.
The Macola databases should definitely use the disaster recovery that Andy talks about above. The tempdb, not so much.
If set to simple, it won't keep a transaction log file.
The Macola databases should definitely use the disaster recovery that Andy talks about above. The tempdb, not so much.
crystalreporting
Instructor
So help me understand this - if someone were to accidentally run a purge process at say 9:15, you could restore to a point in time prior to that (say 9:13) if you weren't using simple recovery mode. Wouldn't this only be of use if no-one else were on the system at 9:13? i.e if you restore the data to a point in time, how do you account for applications that were open or in-process at that time?
Not trying to start an argument here - I'm just trying to understand the benefit of the transaction log in a multi-user Macola environment.
Peter Shirley
Not trying to start an argument here - I'm just trying to understand the benefit of the transaction log in a multi-user Macola environment.
Peter Shirley
Crystalreporting:
To clarify where this post may be heading I wold like to put a couple of things on the table.
1. Dont worry about starting an argument. I don't look at your question that way. Simply an exchange/clarification of information that will allow everyone that reads it to become more informed.
2. If your re-read my lenghty post from above (I normally do not post this long), you will need to understand that if something bad occured at 9:15 you would not be able to restore to 9:13. You would only be able to restore up to the trans log that occured at 9:00. On our large DB (we are about 50 users with a very LARGE DB, 14GB or close in Macola). The average 2-hour trans log is between 9000 and 16,000 KB which takes about 15 seconds to run.
3 For the remainder of this reply I will try to use the workd transaction to mean the SQL transaction. Most SQL types use transaction in reference to the actual SQL transactions that occur. Most ERP/Accounting types refer to transactions as the Macola transaction in AP, GL, AR, IM, etc.
So having said that, The transaction log is logging not the Macola transactions but the back-side or SQL transactions that actually occur at the DB level. Knowing that, you can then see that if at 9:00 a person was ENTERING an order into Macola while the bad posting or corruption was occuring and they have not yet savd the order, no SQL transaction has yet occured. When the restore is done up to the 9:00 trans log the Order they were entering will not be in the system and the next order number may even be wrong.
After a restore, the first thing that should be done before normal useage is to have the useres check for work that they think should be in the system. This could include running reports for open items, open orders, GL postings etc etc etc. Then resume normal operations starting with replacing missing entries in their respective modules. In other words the restore would not be 100% of the work that was actually in progress but 100% of the work that had been committed or transacted at the SQL level. This is better than loosing 100% of the work for the entire day, week, month, year or whenever the last good tape could be found.
Also back to the original tempdb error. I have found that Macola is actually reporting this wrong. The fix is to backup the translog for the current data company in use at the time of the error. The tempdb should be on simple recovery. It is usually the translog for the active DB that causes the problem. The tempdb should never be touched with the exception of the normal nightly backup.
Feel free Peter to post anything you need to to get clarification on this. The subject of maintenance plans and backup models in SQL is one of the most important aspects of maintaining the availablility of your Macola data to your end users.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
To clarify where this post may be heading I wold like to put a couple of things on the table.
1. Dont worry about starting an argument. I don't look at your question that way. Simply an exchange/clarification of information that will allow everyone that reads it to become more informed.
2. If your re-read my lenghty post from above (I normally do not post this long), you will need to understand that if something bad occured at 9:15 you would not be able to restore to 9:13. You would only be able to restore up to the trans log that occured at 9:00. On our large DB (we are about 50 users with a very LARGE DB, 14GB or close in Macola). The average 2-hour trans log is between 9000 and 16,000 KB which takes about 15 seconds to run.
3 For the remainder of this reply I will try to use the workd transaction to mean the SQL transaction. Most SQL types use transaction in reference to the actual SQL transactions that occur. Most ERP/Accounting types refer to transactions as the Macola transaction in AP, GL, AR, IM, etc.
So having said that, The transaction log is logging not the Macola transactions but the back-side or SQL transactions that actually occur at the DB level. Knowing that, you can then see that if at 9:00 a person was ENTERING an order into Macola while the bad posting or corruption was occuring and they have not yet savd the order, no SQL transaction has yet occured. When the restore is done up to the 9:00 trans log the Order they were entering will not be in the system and the next order number may even be wrong.
After a restore, the first thing that should be done before normal useage is to have the useres check for work that they think should be in the system. This could include running reports for open items, open orders, GL postings etc etc etc. Then resume normal operations starting with replacing missing entries in their respective modules. In other words the restore would not be 100% of the work that was actually in progress but 100% of the work that had been committed or transacted at the SQL level. This is better than loosing 100% of the work for the entire day, week, month, year or whenever the last good tape could be found.
Also back to the original tempdb error. I have found that Macola is actually reporting this wrong. The fix is to backup the translog for the current data company in use at the time of the error. The tempdb should be on simple recovery. It is usually the translog for the active DB that causes the problem. The tempdb should never be touched with the exception of the normal nightly backup.
Feel free Peter to post anything you need to to get clarification on this. The subject of maintenance plans and backup models in SQL is one of the most important aspects of maintaining the availablility of your Macola data to your end users.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
crystalreporting
Instructor
Andy,
No problem - I very much appreciate the response. I agree with your comments on the tempdb error as I've seen it reported this way when the transaction log for the Macola database is full.
With regards to simple versus complex recovery, I guess I'd like to give my customers the option and let them decide. Most that I deal with do NOT have full time IT administrators and very few have solid SQL expertise. Some are very remote too, and I have to bear this in mind when considering options for disaster recovery.
Most have also come from legacy systems where they are familiar with the concept of restoring 'from last nights backup'. It's safe to say that some are even geared to work this way i.e. their manual reporting and filing is done daily to allow them to restore from backup and replicate lost work on a day-by-day basis, not an hour-by-hour basis. For some, this is simply a carryover from using the Pervasive database.
With my original question I was really looking to see if there is a tangible 'downside' to using the simple recovery mode. If I understand it correctly, SQL's simple recovery mode provides the same disaster recovery model as Pervasive (for example) and for some of my customers, that's probably a fair trade off.
Peter Shirley
No problem - I very much appreciate the response. I agree with your comments on the tempdb error as I've seen it reported this way when the transaction log for the Macola database is full.
With regards to simple versus complex recovery, I guess I'd like to give my customers the option and let them decide. Most that I deal with do NOT have full time IT administrators and very few have solid SQL expertise. Some are very remote too, and I have to bear this in mind when considering options for disaster recovery.
Most have also come from legacy systems where they are familiar with the concept of restoring 'from last nights backup'. It's safe to say that some are even geared to work this way i.e. their manual reporting and filing is done daily to allow them to restore from backup and replicate lost work on a day-by-day basis, not an hour-by-hour basis. For some, this is simply a carryover from using the Pervasive database.
With my original question I was really looking to see if there is a tangible 'downside' to using the simple recovery mode. If I understand it correctly, SQL's simple recovery mode provides the same disaster recovery model as Pervasive (for example) and for some of my customers, that's probably a fair trade off.
Peter Shirley
I think that the customers not using the Full Backup and capabilities of Macola need to be educated on how to use it. Makes the consultant look intelligent when you can present a money saving simple way of backing up the db. The recovery can be accomplished remotely through TS or whatever. Gives them more faith and confidence in their systems as well.
Have a Merry Christmas
Andy Baldwin
"Testing is the most overlooked programming language on the books!
Have a Merry Christmas
Andy Baldwin
"Testing is the most overlooked programming language on the books!
- Status
Similar threads
- Locked
- Question
- Locked
- Question