June 19, 2015 at 8:55 am
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again. Question : What causes this and how do I fix it?
June 20, 2015 at 8:10 am
I'm puzzled by this "with backup device creating backups with a new transaction log for each day"
How is it possible that it's creating a new physical transaction log file daily? Currently how many log files are associated with this database? Is a 3rd party application performing the backups? And finally, how frequently are transaction log backups running?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 22, 2015 at 5:44 am
Well that is my question. How is it possible and why is it happening? I have been working with SQL for 20+ years and have never seen it before it started showing up about a year ago. We have over 400 customers and so far only a hand full are having this problem. There is a log file for each day of transactions. The last one I found had over 180 days or 180 copies of transaction logs. And according to Microsoft when you restore them they have to be restored in the order they happen. Microsoft has yet not given me a way to resolve this other than replace the entire computer and software. The backups run at 4AM every day. The backup device is initiated by a 3rd party application but the backup itself is handled by the backup device. I have deleted the device and recreated it a couple of time on the advice of forums but nothing has worked. It started showing up with SQL 2008. May or may not be the issue. We had everyone on SQL 2005 prior to this but only for a short while. We did not see any of this on SQL 2000 or SQL 2005.
June 22, 2015 at 5:49 am
What, exactly, do you mean by 'There is a log file for each day of transactions.'?
What are you referring to as a 'log file'? The actual .ldf file? A log of the backups? Something else?
What's your backup strategy? Full backup frequency? Log backup frequency?
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 7:05 am
The ldf file is the one that is duplicating. the backup runs everyday at 4AM when no one is on the system. It does a full backup.
June 22, 2015 at 7:17 am
So you're getting databases with multiple active ldf files?
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 7:22 am
Yes that is correct. If I pull a backup from one of these sites and try to restore it the backup will have the database file plus many log files listed to be restored.
If I go back to the that same site and do a backup manually and select to overwrite then the backup file is much smaller and only has the database file. I will be on a site later today I will grab a screen shot of what I see.
June 22, 2015 at 7:34 am
sault (6/22/2015)
If I pull a backup from one of these sites and try to restore it the backup will have the database file plus many log files listed to be restored.
But that's not what you just said. You previously said that the databases were getting extra ldf files. Now you say that the restore path has multiple log backups associated.
Which is it?
Having multiple log backups for a full backup is perfectly normal, log backups are your 'incremental' backups, designed to allow you to restore databases to point in time. They're essential when a database is in full or bulk-logged recovery models, which, since the entire point of putting a DB into full recovery is to allow for point-in-time recovery is fine.
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 7:46 am
I just logged into one of the sites with the problem and created a new DB called test. I then restored the latest backup into it and it showed the Full Database and under it were 22 Transaction Logs. This is not normal for our backups. All the backups we have ever had showed on database file and one transaction log.
I understand how some could use this to restore to a given point in time but that is not how our backups were meant to be setup. We only want one transaction log and I find nothing in the device setup for backups to do this. A manual backup will do it only if I select to overwrite previous.
I tried to insert a screen shot but it did not work when I previewed it so I hope you understand what my problem is.
June 22, 2015 at 8:28 am
I think maybe you're miunderstanding backups.
Transaction log backups are 'incremental' backups. The full backup is not a backup of the data file, it's a backup of the data files and log files. The transaction log backups are used to restore the database to specific points in time because they contain the record of changes.
For example, if you have a full backup at 8pm and only that full backup, then you can only restore that database to 8pm.
If you have a full backup and 24 log backups (taken at 1 hour intervals), then you can restore that database to 10pm, or 00:15 or any other point.
The number of log backups will be determined by how often the backup tool runs log backups, nothing else. Look at the scheduling of the backup tool, how often it runs log backups should be in there. It's not something that SQL Server controls.
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 8:39 am
That very well could be. I have never seen it do this in Sql 2000 or 2005. All our sites are setup the same. But only a handful have this issue. The backups are happening daily and they work fine except for being huge due to the number of log files that it is backing up. My question remains how do I get it to stop creating so many log files. If it is a setup issue, a backup device issue or whatever we need to find it. This became an issue when a site crashed and we found the last backup was over 12 gigs (2 gig is normal) and it had over 400 transaction logs in the backup.
A audit of all our other sites found 5 with similar issues. A manual backup selected overwrite and fixes it. But it starts up again the next day. As part of the backup the transaction log needs to updated into the database. How do I make that happen?
June 22, 2015 at 8:52 am
No, not 'a large number of log files being backed up', a large number of log backups. Very, very different thing.
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.
As part of the backup the transaction log needs to updated into the database.
Backups don't do that, ever. That's not how SQL transaction logs work.
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 9:12 am
Well you may be right. I have never experience this before so I have no history. But I do need to find a fix. The backups are working because we are getting new zip of the backup file everyday with the newly added transaction log.
All I need to know is how to keep it from creating a new transaction log every day. If the transaction log is not updated into the database what exactly is it used for and why when you restore only the database you have days of transactions missing till you restore each and every transaction log.
June 22, 2015 at 9:19 am
sault (6/22/2015)
But I do need to find a fix.
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 the transaction log is not updated into the database what exactly is it used for and why when you restore only the database you have days of transactions missing till you restore each and every transaction log.
The log is used for the consistency and durability requirements of ACID, to allow transaction rollbacks and failures, to allow SQL to recover from unexpected shutdowns, for things like log shipping, mirroring, replication and many others.
If you restore the database only, then you get the database exactly as it was at the time that full backup was taken. To restore to other points in time, you need transaction log backups.
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 11:05 am
I am still back to how do I stop it. The backups are happening. I verified that. Comparing two sites (one works and one does not) they are identical in setup as far as I can tell.
Is there a way to create a backup device that will do a full backup every time it is called ? I understand how the transaction files are used. But that is not how we have them setup. We only want one transaction log. When the backup happens we want to backup the database and the transaction log as one. So when we restore the backup there is only one transaction log or none at all.
There has to be a way to stop it. 395 sites work. 5 do not. 5 create multiple transaction logs. 395 only have one. The third party software only calls the backup device and it creates the backup. The third party software then zips it and stores it. Nothing super difficult about it except the creation of a transaction log for each day that SQL runs.
So far I have only been able to determine that if I do a manual backup it will start the process all over. All the transaction logs are added to the database and a new transaction log is started. I don't see why this has been so difficult to resolve.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply