May 28, 2010 at 11:33 am
Gary7512 (5/28/2010)
Hi. What can someone do if they don't want a big transaction log? Our db is 7GB and the log is 10GB. There's no way we're going to rollback any further than 60 minutes into the past so 95% of the log file is a waste of space, especially when it comes to taking a file backup each night.Everytime I truncate the log down to 1GB it jumps back to 10GB the next day (after the scheduled db backup). The log has 92% space used.
The reason we'd never use the log to rollback is because our app is a website which has 1000's of users so we'd never want to undo the good transactions for the sake of 1 user who may have screwed up their data. I've followed various tutorials on truncation and log backups but ours always bloats up to 10GB. Any help would be very useful. Thanks.
Gary.
Is your database in SIMPLE or FULL recovery model? If you can afford to lose up to 30 hours of data in a disaster, then you can use the SIMPLE recovery model and leave the log file alone (after it is sized appropriately).
If that is too much risk, then use FULL recovery model and take frequent (every 15 minutes) transaction log backups.
Either way, if the log file grows to 10GB - then it needs to be that size. Shrinking the log repeatedly and letting it grow is just causing performance issues and file fragmentation. Not to mention the large number of virtual log files that is going to cause additional performance issues.
Read the article I link to in my signature on managing transaction logs. Also, you can review the articles here: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 12:11 pm
Alternatively Why do I need a transaction file at all?
For example, I have a database of literally millions of records. This database is updated in batch mode once or twice per year. The records are not updated at any other time.
All of the rest of the activity in the database consists of reads, yes its just used for reporting.
Since I back it up just prior to and immediately after the updates, why do I need transaction logs at all?
And is there any way to tell MS/SQL to omit the transaction logging?
May 28, 2010 at 12:33 pm
Tech 23202: Simple recovery mode minimizes the amount of data written to the log file and does not allow transaction log backups. I would think this is your best option for the situation you described. As to not needing the log file at all, I don't think I've ever tried to get by without one.
May 28, 2010 at 2:00 pm
Hi Jeffrey. We're using full recovery. We're taking an incremental backup of the entire server every 30 minutes so we can restore from bare metal or just a database file from any point in time. (We've virtualised the db server making backups 10x easier!) So if simple recovery will help keep the log file down we'll give that a shot. Thanks for your advice.
Gary.
May 28, 2010 at 2:15 pm
Gary7512 (5/28/2010)
Hi Jeffrey. We're using full recovery. We're taking an incremental backup of the entire server every 30 minutes so we can restore from bare metal or just a database file from any point in time. (We've virtualised the db server making backups 10x easier!) So if simple recovery will help keep the log file down we'll give that a shot. Thanks for your advice.Gary.
There are no incremental backups in SQL Server. There are:
Full Backup
Differential Backup
Transaction Log Backup
I am not sure what you mean by incremental backups. If you are actually performing differential backups, then you are not managing the transaction logs appropriately when in FULL recovery model. When in FULL recovery model, you must perform frequent transaction log backups.
If you are running Transaction Log backups - then you are hurting yourself by shrinking the log, since it will just grow as large as it needs to be to handle 30 minutes worth of transactions. If the transaction log is too large, you could increase the frequency to every 15 minutes.
Switching to SIMPLE won't reduce the size of the transaction log. It just removes the requirement to backup the transaction log. You won't be able to restore to a point in time, but if you are okay with losing a days worth of data that would be fine.
The transaction log will not shrink by itself - so making these changes won't make it smaller. If you make these changes, then you want to perform a one-off shrink to your normal size (see Kimberly's articles on how to size a transaction log). Then, you would monitor the size and once you have a stable size, perform another shrink - and manually grow to a size just larger than the size needed to handle your day to day operations.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 2:30 pm
Jeffery, sorry I probably didn't explain that well. Our db server is a VM on Hyper-V. The Hyper-V host does an incremental backup using Windows Server Backup of everything including the db VM every 30 mins. In the event of a problem we can restore the entire host or the db VM or just the db files.
May 28, 2010 at 3:08 pm
I've found it best to refer to transaction log backups as transaction log/incremental backups. I work with IT folks from many different backgrounds, along with managers with little technical background and it's a constant source of confusion. Since we don't have an official DBA and some of those who confuse the two are senior people not inclined to listen for very long, using the transaction log/incremental backup term helps a bit.
May 28, 2010 at 3:51 pm
Gary7512 (5/28/2010)
Jeffery, sorry I probably didn't explain that well. Our db server is a VM on Hyper-V. The Hyper-V host does an incremental backup using Windows Server Backup of everything including the db VM every 30 mins. In the event of a problem we can restore the entire host or the db VM or just the db files.
Have you tried restoring the system from these backups? I would be surprised if the database files could actually be restored, since they are locked by SQL Server and performing any kind of snap on the files without freezing SQL Server will not work.
Since I don't know what is actually being done and how - I really cannot say whether or not this is appropriate.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 5:44 pm
Jeffrey, that's a fair question. The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! 🙂
May 28, 2010 at 5:49 pm
Gary7512 (5/28/2010)
Jeffrey, that's a fair question. The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! 🙂
Oh, definitely need to test - I don't think VSS is going to work well with SQL Server database files. I am not sure about that, but yeah - test it to make sure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 8:02 pm
Gary7512 (5/28/2010)
The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! 🙂
Do it now! A file backup taken with VSS will almost certainly not enable you to recover any data at all if disaster strikes.
A SQL Server backup needs to be transactionally consistent. A file backup has no way to achieve that.
Separate point: once a transaction has committed, you cannot undo it from the transaction log. A complete sequence of log backups from the last full or differential backup is required to enable you to recover to the point of failure.
You absolutely must have proper SQL Server backups of your data to recover from a disaster. You should also regularly test that your backup strategy works in practice.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 8:08 pm
tech 23202 (5/28/2010)
Alternatively Why do I need a transaction file at all?
Because SQL Server will not run without one 🙂
The transaction log records absolutely every change made to a database - how ever it occurs, with enough information to undo (rollback) any partially completed operations in case of, say, a power failure.
For example, I have a database of literally millions of records. This database is updated in batch mode once or twice per year. The records are not updated at any other time. All of the rest of the activity in the database consists of reads, yes its just used for reporting. Since I back it up just prior to and immediately after the updates, why do I need transaction logs at all? And is there any way to tell MS/SQL to omit the transaction logging?
Set the database to READ ONLY. If no changes are possible (because the database is read only), SQL Server will not need to write to the transaction log (which can be as small as you like). Another side benefit is that SQL Server will not take any locks.
You will still need a physical log file, but there will be no activity on it (at least until you take it out of read only mode for a batch update).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2010 at 7:42 am
Paul White NZ (5/28/2010)
Gary7512 (5/28/2010)
The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! 🙂Do it now! A file backup taken with VSS will almost certainly not enable you to recover any data at all if disaster strikes.
A SQL Server backup needs to be transactionally consistent. A file backup has no way to achieve that.
My colleague who is more technical than me says: "The way it works is that the Hyper-V VSS writer on the host tells VSS in the guest to get ready for a snapshot, so any VSS enabled apps running in the guest (including SQL) do prep themselves. If any VSS writer in any of the guests fails, this causes the entire backup to fail which is reported immediately by WSB (Windows Server Backup)."
So Windows is telling SQL to say "cheese" for a photo, so it has several seconds to comb its hair in preparation. i.e. it's being told to put itself into a state where a backup can be made. I feel more confident now this has been explained to me but I do accept we need to test out a restore to make sure it's working. It's a lot cheaper than testing out the airbags of your car! (The manufacturer tells you the airbags will work but no one ever tests them to make sure.)
May 29, 2010 at 8:19 am
Gary7512 (5/29/2010)
My colleague who is more technical than me says: "The way it works is that the Hyper-V VSS writer on the host tells VSS in the guest to get ready for a snapshot, so any VSS enabled apps running in the guest (including SQL) do prep themselves. If any VSS writer in any of the guests fails, this causes the entire backup to fail which is reported immediately by WSB (Windows Server Backup)."So Windows is telling SQL to say "cheese" for a photo, so it has several seconds to comb its hair in preparation. i.e. it's being told to put itself into a state where a backup can be made. I feel more confident now this has been explained to me but I do accept we need to test out a restore to make sure it's working. It's a lot cheaper than testing out the airbags of your car! (The manufacturer tells you the airbags will work but no one ever tests them to make sure.)
Some higher-end SAN systems provide a similar service, issuing a Freeze & Thaw I/O commands at the appropriate moments. This provides a 'crash consistent' copy of the database - which is not the same as a fully transactionally-consistent backup. There's a whole bunch of complexity around consistency groups and so on.
I don't think I am alone amongst DBAs in being inherently sceptical of these 'magic' solutions.
Please do test this urgently - and test recovering from a 'snapshot backup' taken when high write activity is occurring - be sure to verify that 'in-flight' transactions are handled properly.
Unless the 'magic' backups have some decisive advantage for your business I would personally always choose SQL Server backups.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2010 at 8:36 am
Thanks Paul, I will take note of what you're telling me. It does seem like a good magic solution, but I suppose some fables warn the use of magic for self-gain comes at a price. :unsure:
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply