File Size Issues
-
I have recently changed my database from simple recovery to Full recovery. I have created a new backup plan which backups the full database at night 4 differential backups and a transaction backup every 20 minutes throughout the day. However my initial problem was that my backup device file was getting too large which I found to be the transaction logs. One transaction log in the device was 2GB, if this happened every 20 minutes I would have run out of space so I turned it off.
Now my ldf file was not being truncated and that grew too large for the disk resulting in down time. I managed to shrink the ldf file and set the file to a restricted size. However this then became full again resulting in down time.
My question is how can I configure the database to full recovery whilst keeping file sizes down?
Point to note is that this is on 3 databases that join to work as one in a very large system. All have similar issues but with different sizes.
-
In the full mode, log files grow until backed up and once the file has grown it does not resize as SQL Server automatically assumes that the current size of the file optimal.
My suggesstion would be try to determine the optimum size for the logs. You do based on assumptions of how many transactions will occur between back ups/checkpoints of the log file.
Once that is determined, backup the log file and resize it to the optimal size that you determined.
Remember, to take an immediate FULL backup of the database after you truncate the log. If your estimates were correct, then the size of your log file should not grow as it is being backed up at the proper interval.
Marvin Dillard
Senior Consultant
Claraview Inc -
My problem is that the transaction backups are filling up the backup device with 2GB every 20 minutes. I read that changing the verbose settings of the transaction log might help but do not know anything about this.
-
Don't use backup devices. Run each log backup to a new file so you can tell what the transaction size for that period of time (log backup -> log backup) and then plan for that.
2GB every 20 minutes would be an incredible load of transactions. are you potentially altering 2GB worth of data every 20 minutes?
-
Agree with Steve, collecting 2G of changes in 20 minutes is a heck of a throughput. What I suspect is that your log file size is already 2Gs and since it won't shrink, your log backup will always be 2Gs.
Marvin Dillard
Senior Consultant
Claraview Inc -
MD (5/12/2008)
Agree with Steve, collecting 2G of changes in 20 minutes is a heck of a throughput. What I suspect is that your log file size is already 2Gs and since it won't shrink, your log backup will always be 2Gs.Shouldn't be. The size of the log backup will be the size of the data in the log file, not the size of the log file itself.
John
-
I do not quite follow you why you need both differential and transactional backups.
To solve your disk issue, you may increase the frequency of your transactional backups, say, have your transactional backups every 10 min.
-
John Mitchell (5/12/2008)
MD (5/12/2008)
Agree with Steve, collecting 2G of changes in 20 minutes is a heck of a throughput. What I suspect is that your log file size is already 2Gs and since it won't shrink, your log backup will always be 2Gs.Shouldn't be. The size of the log backup will be the size of the data in the log file, not the size of the log file itself.
John
I am looking at the contents of the backup device to see the size of the transaction backup not a file. I was thinking that this would just be all transactions over the last 20 minutes and nothing else. Or is it just a backup of the .ldf file?
-
Daryl
I've always assumed it was the size of the log file and planned for it to be that way, however, john posted earlier that it wasn't, so I'll have to do a test to determine if it's the size of the file or the transactions in the file. I'll let you know after I fill up a few logs in my Dev environment
MD
Marvin Dillard
Senior Consultant
Claraview Inc -
I do not quite follow you why you need both differential and transactional backups.
Doing differential backups will reduce the number of backups to be restored from and make it less likely that the restore operation will fail on an unsuccessful or lost log backup.
Darryl Sibson (5/12/2008)
I am looking at the contents of the backup device to see the size of the transaction backup not a file. I was thinking that this would just be all transactions over the last 20 minutes and nothing else. Or is it just a backup of the .ldf file?Not sure what you're asking here. If you back up the log with default options, it will truncate the log as well, so yes it will only back up transactions committed in the last 20 minutes. But it won't be an exact backup of the .ldf file.
John
-
John is correct. It is only a backup of the committed transactions, not the entire LDF. So, back to the original issue, 2 Gigs of committed transactions in 20 minutes is a heck of a lot of transactions. Is there anything else going on such as index rebuilds?
As for differential and trans backups. I do both, myself, I take a differential every 12 hours, the reason for differential is speed of recovery. Say you are doing transaction log backups every 15 minutes, that's four per hour, or fourty-eight in a twelve hour period. Would you rather restore one differential backup or 48 trans backups?
Marvin Dillard
Senior Consultant
Claraview Inc -
I've tested this already in our training environment which is why I use a backup device. Rather than doing each transaction separately I can roll back the database by using the device by ticking the full database backup the latest differential and then all transaction backups up to the point in time I want to restore. I can then set the database to restore with recovery and do all in one go.
The differential backups are every 3 hours between 9am and 6pm so that gives me 4. With Transaction logs every 20 minutes giving me a max of 9 between each differential. The Full backup occurs every night and overwrites everything in the device, this is OK though as the device is now on tape in a rotation of 5 other tapes. This means we have 5 days worth of fully backed up databases.
Although tested in the training environment which has next to no transactions, all works fine. However when applied to the live DB the transaction log file .ldf grows beyond the fixed limit. Which stops transactions taking place, or if not fixed outgrows disk space. The backup device also grows too large because of the size of the transaction backup in the device.
-
Darryl Sibson (5/14/2008)
I've tested this already in our training environment which is why I use a backup device. Rather than doing each transaction separately I can roll back the database by using the device by ticking the full database backup the latest differential and then all transaction backups up to the point in time I want to restore. I can then set the database to restore with recovery and do all in one go.I think you can do this even if you don't use backup devices - give it a try.
Although tested in the training environment which has next to no transactions, all works fine. However when applied to the live DB the transaction log file .ldf grows beyond the fixed limit. Which stops transactions taking place, or if not fixed outgrows disk space. The backup device also grows too large because of the size of the transaction backup in the device.
Then you have two choices: either allow more disk space for log and log backups (easier to manage if you use individual files rather than backup devices) or cut down on the number or sizes of the transactions. If you don't know what transactions are causing the log to grow so big, you could use Profiler to see what's going on.
John
-
John is correct, this can be done without the use of backup devices. You can backup the database directly to a local file and then copy that file onto your tape drive.
The system I use is to backup the file locally, and then copy it over to a network drive after backed up. The network admin shop then backs up the network share drive onto tape. I keep two days of backups on the local drive, two weeks of backups on the network drive and I am told they keep the tapes forever without over writing them.
No matter which strategy you decide to follow, test your backup via a restore process
Marvin Dillard
Senior Consultant
Claraview Inc -
OK in the test environment with the smallest database of the group attached is the contents of the device.
As this was the test database I generated some transactions before 3:17 (position 7) by doing a re-index of tables. I ran the transaction backup task again and can see that the log size has gone down but still larger than some even though the last transaction log was only a minute ago. I still cant understand why the backup taking position 4 is so small compared to the others. Also note that position 3 & 6 are the same size as are 8 & 12. Does anyone have any answers as to why this is the case? As my understanding is the transaction log is all successful transactions since the last transaction log how can the backup taken in position 10 be larger that the one in position 4?
Regards,
Darryl
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply