June 22, 2015 at 1:41 pm
sault (6/22/2015)
I am still back to how do I stop it.
Once more with feeling...
GilaMonster (6/22/2015)
Check the scheduling for the 3rd party backup tool. SQL does not control the frequency of backups, if there are multiple log backups been taken, it's because the 3rd party backup tool is taking them because it has been told to do so.
If you can't see anything obvious, contact the 3rd party backup tool's customer support people and ask them where to look to see the scheduled backups.
Is there a way to create a backup device that will do a full backup every time it is called ?
Backup devices have nothing to do with what kind of backup is taken. The define the location the backups are written to. That's it. You don't call a backup device, backups are written to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2015 at 1:52 pm
We are that 3rd party. We setup the backups to run daily at 4AM. I understand that the backups use the device to backup to. It was just a figure of speech. Meaning that a simple script is run to backup the selected database to the device. It is like all of two lines.
Looks like I am back to square one. No one seems to know why one site has multiples and others have one transaction log. There has to be a way to backup the database so the transaction logs are incorporated into the main database base. As I do when I back it up manually and select to overwrite. My next idea is to restore a backup onto one of our test beds and see if I can recreate the problem. I am thinking the problem is not in the database itself but in the configuration of SQL.
June 22, 2015 at 2:02 pm
First of all, let's get the terms right to avoid confusion. A "transaction log" or "log file" is one of the database files. You are talking about "log backups" which is a backup of the transactions in the active portion of the log file.
You are seeing multiple log backups because someone has configured log backups for the database.
If I configured log backups for a database, and someone stopped them, that person would be fired. Log backups protect the database form data loss.
DO NOT STOP THE LOG BACKUPS. It is not broken, so please don't try to fix it.
June 22, 2015 at 2:22 pm
The log backups should not multiply to 100's. That is my problem. They never import or write the data into the main database unless a manual backup is done. If left alone they cause the system to crash. like what happened to bring this to our attention.
June 22, 2015 at 2:37 pm
sault (6/22/2015)
The log backups should not multiply to 100's. That is my problem. They never import or write the data into the main database unless a manual backup is done. If left alone they cause the system to crash. like what happened to bring this to our attention.
I'm lost! Totally lost!!!! :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 22, 2015 at 2:39 pm
No, there needs to be a maintenance job to delete old backups (older than your SLA for keeping data on-hand).
If you want, there is one a script you can use that is part of the standard backups script I wrote years ago. You just need to tell it where to look and what file extension to use: http://www.sqlsoldier.com/wp/sqlserver/day13of31daysofdisasterrecoverystandardbackupscripts
June 23, 2015 at 2:47 am
sault (6/22/2015)
They never import or write the data into the main database unless a manual backup is done.
Seriously, you need to spend some time reading up on what backups actually do and how SQL actually works. Log backups have nothing to do with writing data into the main database, they never do that, that's not in any way what backups do, that's not how SQL works at all.
When you make a manual backup, you're just overwriting all the existing backups, nothing more than that. That means by doing the manual backups you're probably removing the ability to restore this DB to any point before the manual backup. I do hope these aren't critical databases.
If you have hundreds of log backups happening, it's because your backup tool (the 3rd party tool you haven't named) is scheduled to make those backups because someone set them up.
Some basic reading on backups and logs:
https://www.simple-talk.com/books/sql-books/sql-backup-and-restore/ This one's a good, basic coverage of backups. Should help you understand what you're actually seeing and how backups work.
https://www.simple-talk.com/books/sql-books/sql-server-transaction-log-management-by-tony-davis-and-gail-shaw/ What transaction logs are used for, how they work and how they should be maintained
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2015 at 5:43 am
I appreciate all you suggestions and I know you are really trying to help. I think the big problem is that I can not relay the real problem very well. I have actually been working with SQL for well over 20 years and have never ran across a backup problem before. So I think you are right I need to dig out some of my SQL reference manuals and see what I can find.
If the transaction logs are never part of the database then what is in them that brings the database up to date when they are restored? For instance I have a database that was backed up on June 1 with 50 transaction logs. If I understand you correctly, and the backups have been happening on a daily basis then I should be able to restore the database back to 50 days prior to the backup. From my testing this is actually what I found. So that is what lead me to believe that there was data in the transaction logs that has not been written to the main database.
Even with that understood, I would like to ask when does the transaction logs quit. I seen a backup that had over 400 transaction logs in it. To restore it I had to select each and every one of them along with the main database and then do anther backup to compress it, or update the main database, or whatever the transaction logs do to incorporate into the main database. At some point the transaction logs have to stop multiplying. I can not afford to assign resources to check on backups to make sure they have not create hundreds of logs.
I know this horse is long dead but I have not gotten an answer as to why 395 sites only have 1 transaction log incorporated into each backup and they are good backups. And why 5 other have anywhere from 10 to over 400 transaction logs in the bak file. What is the trigger that causes this?
I promise I will take your suggestion to do some reading and to answer your question these are very critical databases that is why they are backed up daily and on a regular test schedule. We just did not get to the site that crashed before it ran out of drive space. The backups were taking up over 10 gigs of storage per backup and it was backing up daily. Doesn't take log to fill up a drive at that rate.
Thanks Gail for trying to help with this issue.
June 23, 2015 at 6:21 am
sault (6/23/2015)
If the transaction logs are never part of the database then what is in them that brings the database up to date when they are restored?
I didn't say transaction logs are never part of the database, that would be false, they're always a part of the database. I said, a couple of times, that they are not written to the DB *by a backup*. The transaction log is used for consistency, recoverability, rollbacks and a few other things.
Transaction log backups (which are not the same as transaction logs) are used for restoring to a specific point in time.
For instance I have a database that was backed up on June 1 with 50 transaction logs. If I understand you correctly, and the backups have been happening on a daily basis then I should be able to restore the database back to 50 days prior to the backup.
No, you can never go backwards in time from a full backup. What log backups allow you to do is to restore a full backup taken 10 days ago, then restore log backups and to end up with a database restored to a point any time between 10 days ago and the latest transaction log.
So that is what lead me to believe that there was data in the transaction logs that has not been written to the main database.
Nope, that's not what they are, that's not how they work, please see the second link I posted.
Even with that understood, I would like to ask when does the transaction logs quit.
Meaningless question, as log backups do not ever run automatically. SQL does not created them by itself. If you have log backups then your backup tool is explicitly running log backups and creating the files.
I seen a backup that had over 400 transaction logs in it.
No, you have a backup with over 400 log backups. Very different thing.
To restore it I had to select each and every one of them along with the main database and then do anther backup to compress it, or update the main database, or whatever the transaction logs do to incorporate into the main database.
No, absolutely not. When you took another backup, all you did was overwrite the full backup and all 400 log backups. You did not compress it. You did not update the main database (because that's not what log backups do). You overwrote the existing backup with a new one.
At some point the transaction logs have to stop multiplying. I can not afford to assign resources to check on backups to make sure they have not create hundreds of logs.
They'll stop when the backup tool you are using stops creating them. Which will be when you go into the settings and schedules of that backup tool and change them.
If you really want a critical database that can't be restored to anything other than the full backup time, that is. That's not usually what is required for critical databases.
I know this horse is long dead but I have not gotten an answer as to why 395 sites only have 1 transaction log incorporated into each backup and they are good backups. And why 5 other have anywhere from 10 to over 400 transaction logs in the bak file. What is the trigger that causes this?
Once more with feeling....
Check the backup tool you're using!
SQL does not ever create log backups automatically. If your backup tool is creating log backups for 5 databases then it is because the backup tool is set to run log backups on a regular basis for those 5 databases. The only place you're going to find an answer for this is in the 3rd party backup tool that you are using.
If you don't know where to look, contact customer support and ask them to help you find the schedule and backup type settings.
These are very critical databases that is why they are backed up daily and on a regular test schedule.
Very critical databases that can tolerate up to 24 hours of data loss in the case of a disaster? With daily backups, if something goes wrong you can lose up to a full day of work.
Last time I worked with critical databases, the data loss allowance was under 10 minutes, which meant full backups daily and log backups every 5 minutes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2015 at 6:50 am
I bow to your expertise. I have downloaded one of your book suggestion and I am going to go through it . Transaction log management. It appears to be the best resource to help me get answers.
Since we write the backup program and there is nothing in it but to do a backup using the backup device I have some work ahead of me to get educate on SQL backups. Funny this problem has never come in before.
Yes they are critical database. They handle money for companies but there is other redundancies built in besides the SQL database. However since learning more about the transaction logs that may be changes to writing them much more often. I will know more once I get though your book.
I am glad you picked up this thread and I hope the weather is nice in Johannesburg! It is hot here in the States. Thanks for all your help and I will respond back once I am able to figure out what is going on and how it all happened.
June 25, 2015 at 5:43 am
I have found out why 5 sites have so many Transaction logs. I read the book SQL Server Transaction Log Management and it explained it as a runaway transaction log. I won't go into the details here since it will be unique for each situation but it is not back close to normal The next couple of days will prove that out but I wanted to update the forum in case others have the same problem. If you have way to many VLF ( virtual log files) check out the book. It is a quick read and full of good info on a subject most of us rarely deal with. At least I had not. I did a full backup. Then a log backup. I ran some of the test scripts from the book to get a better idea of what was happening. We have a routine that purges old data daily and in this case it was having issues. Once I found and fixed it I was able to reduce the size of the transaction log and put things back to normal. When I mentioned this to other SQL guys that I went to for help they agreed that could of been the problem. Seems pretty simple now. I am going to keep reading on the transaction log so I know how to use it in case of another crash and we can recover with a few minutes of the crash instead of 24 hours. This is the 4 forum I have ask this question in and I ask contacted Microsoft but no one mentioned runaway transaction log till I read the book.
Thanks again Gail! You have educated an old SQL man.
June 25, 2015 at 5:47 am
I'm going to disagree with you there.
Large transaction log will not result in lots of log backups.
Lots of VLFs won't result in lots of log backups.
You get log backups when something run BACKUP LOG <database name> TO DISK = 'File name here' and that will generate one log backup no matter how many VLFs you have in the transaction log, no matter how large the log is.
And again, you do not have sites with multiple transaction logs. You have sites which are getting multiple log *backups*. Massive difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2015 at 7:05 am
I did not mean to say I had lots of log backups. I did not. I had lots of VLF's. I had a very large log file. Once the problem was found that was causing it and it was "committed", I was able to reduce the log file and as of this morning it appears to be running normally. It was definitely a runaway log file. Something else to add to our check list. 1 down 4 to go.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply