January 7, 2011 at 12:09 pm
cengland0 (1/7/2011)
WayneS (1/7/2011)
Exactly. Doing a t-log backup will clear the VLFs (if possible), so that they can be reused. The t-log grows when it's out of VLFs. You should be able to run with a relatively static t-log file.It won't hurt to run the DBCC command in Kimberly's blog to see how many VLFs you have, etc.
And how would you test a major change like this on a production server? Our fail-over server is the one we backup the production server to and do not have a third server to backup the fail-over for testing.
We do have several other servers lying around with smaller amounts of data but we don't have to shrink those logs because we don't dump as much data into those tables. This is the only server we have with this problem.
Setup a maintenance plan to do transaction log backups every 15 minutes. And monitor the growth of your transaction logs.
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
January 7, 2011 at 12:21 pm
mtassin (1/7/2011)
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
I disagree -- it is a major change. If it doesn't work as planned, then the log can fill up to capacity and no more loggable transactions can be performed and that would shut down our production server.
January 7, 2011 at 12:26 pm
cengland0 (1/7/2011)
mtassin (1/7/2011)
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
I disagree -- it is a major change. If it doesn't work as planned, then the log can fill up to capacity and no more loggable transactions can be performed and that would shut down our production server.
If you read up on what a transaction log backup is, and you schedule them to be frequently done this won't happen. I've got 20 SQL instances with several thousand databases all taking 15 minute transaction log backups. The Transaction logs have not grown at all since I put this in play. The databases range in size from 100 GB down to 500 MB.
But suit yourself. I've got point in time recovery and a whole lot more of a safe feeling than I did when I came in and stopped all the transaction file truncation jobs that were running here when I started 3 years ago.
January 7, 2011 at 12:39 pm
Thankfully I read Kimber's blog and remember seeing this.
January 7, 2011 at 12:51 pm
cengland0 (1/7/2011)
mtassin (1/7/2011)
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
I disagree -- it is a major change. If it doesn't work as planned, then the log can fill up to capacity and no more loggable transactions can be performed and that would shut down our production server.
I view it as a change but a change for the good. This provides less disk requirement, point in time recovery and is better than shrinking the logs. If the logs grow out of whack and you don't catch, you have other issues that need to be resolved. In either scenario - if you don't catch it - you are looking at a serious problem. At least with log backups, you can recover to a point in time and the likelihood of this happening is significantly lower than when not backing up the transaction log.
Alternatives are to perform a combination of diff and full db backups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2011 at 12:56 pm
cengland0 (1/7/2011)
mtassin (1/7/2011)
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
I disagree -- it is a major change. If it doesn't work as planned, then the log can fill up to capacity and no more loggable transactions can be performed and that would shut down our production server.
It is a major change to your system as it affects recovery.
however, if you are not running them now, are you in simple mode? Do you run out of space now? Adding in log backups will not cause an increase from your normal activity. that being said, set them up, run when you can monitor the server and if there is an issue, go back to what you were doing.
January 7, 2011 at 1:05 pm
CirquedeSQLeil (1/7/2011)
I view it as a change but a change for the good. This provides less disk requirement, point in time recovery and is better than shrinking the logs. If the logs grow out of whack and you don't catch, you have other issues that need to be resolved. In either scenario - if you don't catch it - you are looking at a serious problem. At least with log backups, you can recover to a point in time and the likelihood of this happening is significantly lower than when not backing up the transaction log.Alternatives are to perform a combination of diff and full db backups.
Never said it wasn't a change, just that I don't view implementing Transaction Log backups as a major change in terms of disrupting users. I do consider it a good change.
I've got a combination of all three happening.
Fulls nightly
Diffs hourly
Tlogs every 15 minutes
I do love P.I.T. recovery when necessary.
January 7, 2011 at 1:07 pm
Steve Jones - SSC Editor (1/7/2011)
cengland0 (1/7/2011)
mtassin (1/7/2011)
Transaction log backups are not a major change. It's a backup. SQL server does the rest.
I disagree -- it is a major change. If it doesn't work as planned, then the log can fill up to capacity and no more loggable transactions can be performed and that would shut down our production server.
It is a major change to your system as it affects recovery.
however, if you are not running them now, are you in simple mode? Do you run out of space now? Adding in log backups will not cause an increase from your normal activity. that being said, set them up, run when you can monitor the server and if there is an issue, go back to what you were doing.
Perhaps, but if he's not doing T-log backups (which is pretty obvious if he's both not aware of them, and truncating the t-log to get around the issue), it doesn't affect his current likely full backups that he's using for recovery, it just gives him an additional option. 🙂
January 7, 2011 at 3:40 pm
CirquedeSQLeil (1/7/2011)
Alternatives are to perform a combination of diff and full db backups.
If he's in full recovery mode diff and full backups won't help with trans log size. If he's in simple mode all he needs is to do regular checkpoints. Full and diff backups aren't relevant to transaction log size, just to how recoverable the databases are (and how long recovery can take).
Cengland, are you doing any replication (that can cause transaction logs to get big if you have a poor network and/or the wrong sort of log synchronisation)? Do you have any very long running transactions that happen often enough to cause excessive log growth? Are you doing regular transaction log backups already (if not, try doing them every 5 or 10 or 15 minutes and see what happens - unless you're in simple recovery mode in which case run regular checkpoints instead)? How big are your virtual log files - if they are too big (more than about 0.5GB) that can cause all sorts of problems.
Tom
January 7, 2011 at 4:43 pm
The only situation I could think of where I would understand scheduled log shrinking would be when the following factors combine:
1. Two databases each have a peak usage period. During that period, the log has to be large. The rest of the day, the log can be smaller.
2. These peak periods are scheduled and guaranteed to never coincide.
3. The amount of available disk space is sufficient for the tran log of one DB at max size and one other DB at regular size, but not for both DBs at max size.
4. There is no budget for more disk space.
So for instance, you have two DBs with each a tran log of 2 GB during normal operation, that grows to 20 GB during peak time, and a total of 30 GB disk space. Without shrinking the logs, you'd run out of disk space when the second DB starts to increase its tran log to 20 GB. By shrinking each expanded log before the other one starts to grow, you manage to keep everything running.
But I would not consider such a setup robust, and I would urge management to invest in extra storage space.
January 7, 2011 at 5:03 pm
Nice question Steve, Its also good to see how much debate this has generated.
Gethyn Elliswww.gethynellis.com
January 7, 2011 at 6:20 pm
Tom.Thomson (1/7/2011)
Cengland, are you doing any replication (that can cause transaction logs to get big if you have a poor network and/or the wrong sort of log synchronisation)? Do you have any very long running transactions that happen often enough to cause excessive log growth? Are you doing regular transaction log backups already (if not, try doing them every 5 or 10 or 15 minutes and see what happens - unless you're in simple recovery mode in which case run regular checkpoints instead)? How big are your virtual log files - if they are too big (more than about 0.5GB) that can cause all sorts of problems.
I do replication but that's not why the transaction logs get big. Here's the setup:
Server A (production)
Server B (production failover)
Both servers are set to full-recovery mode.
Server A gets populated with data from several sources all throughout the day and hundreds of reports are generated off this data. This server is critical but the data is not (data is populated off other servers throughout the company so it's easy to get again but time consuming). So if Server A goes down, we will use Server B as our production server.
Server A's databases get backed up on Tape for permanent storage as well as on Server B. It is restored on Server B so we have a copy of what was in production the day before. We tried log shipping but that puts Server B into read-only mode and our developers also use Server B for query testing and need insert and update capabilities.
Since Server A gets data from several sources via SSIS packages, the database that contains the raw data gets very large transaction logs. As you are aware, any insert or update to a table gets put into the transaction logs and we populate about 115 Gigs a day. Even though the transaction logs are on a different SANS drive, we do not have much space available so the only option is to frequently backup the database and then manually shrink those logs so they can be emptied and ready for the next chunk of data.
This process seems to be working fine. I cannot see why it makes much of a difference on how many VLFs I have in my log file because it would still get filled up if I had just 1 VLF or 5,000.
January 7, 2011 at 6:32 pm
Hugo Kornelis (1/7/2011)
But I would not consider such a setup robust, and I would urge management to invest in extra storage space.
We tried that and our request was denied. This is a very robust server and the type of raid SANS system that is used is extremely expensive. Our department gets billed monthly for the hardware that we use and we do not generate revenue for the company so the budget is tight.
Actually, it was cheaper for us to buy a shared server to populate the raw data and then transform it how we need to and then copy that to the production server. Unfortunately, the latency between the two servers is too much to make that practical. The cheaper shared servers are in a different city than the dedicated servers. It takes 5.8 days to transfer just one of the databases and we need our data more up-to-date than that.
January 7, 2011 at 7:54 pm
cengland0 (1/7/2011)
Since Server A gets data from several sources via SSIS packages, the database that contains the raw data gets very large transaction logs. As you are aware, any insert or update to a table gets put into the transaction logs and we populate about 115 Gigs a day. Even though the transaction logs are on a different SANS drive, we do not have much space available so the only option is to frequently backup the database and then manually shrink those logs so they can be emptied and ready for the next chunk of data.
This process seems to be working fine. I cannot see why it makes much of a difference on how many VLFs I have in my log file because it would still get filled up if I had just 1 VLF or 5,000.
When you backup the Transaction Log file you've emptied out all the amount that you can shrink it by. Just le the file be, you're hampering performance by forcing it to grow back to the size that it needs to store logs of transactions between transaction log backups. If you use the basic reports on SQL 2005 or later for disk usage you will see that your transaction log unused area is likely most if not all of the Transaction log right after a transaction log backup. That part of the t-log if re-used after the t-log backup.
Or are you using BACKUP LOG WITH TRUNCATE_ONLY when you backup the transaction logs? If you are, then just convert the database to simple recovery mode, at that point it's the same thing.
January 8, 2011 at 8:54 am
Mark has written a nice suggestions, but it would be nice to know *how* you back up the database and then shrink the log.
- a full backup does not clear the transaction log
- switching the simple mode breaks your recovery chain
- shrinking the log and having it grow again seems wasteful.
- clearing the log out allows it to be re-used. It will not continue to grow and grow, if that is your fear.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply