| Author |
Topic |
|
PerS
Starting Member
7 Posts |
 Posted - 2012-02-02 : 09:34:01
|
| HiI am a newbee at SQL server so keep that in mind when you read and hopefully answer my question. :-)At my new job I'm responsible for administrating a SQL 2008 server. Right now I'm having a problem controlling the size of the log. (It's growing slowly but surely.) Is there a way to do that without changing the recovery model for all databases? Right now I have a backup task for several databases of all three different recovery models. The backup is run daily and while responsible for this the backup-size has been stable and the log-size has grown a couple percent. Is this my answer:http://database.blogs.webucator.com/2010/07/14/transaction-log-backups-in-sql-server-2008/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-02 : 09:39:09
|
| If you are using SIMPLE Data Recovery Model the LOG file will be governed by your largest transaction(s). If it is growing, too much, you will need to alter the way your largest transactions are handled (e.g. by doing that task in smaller pieces).If you are using FULL Recovery Model then your largest transaction is still important, but so is the interval between log Backups. 15 minutes is a good interval. Here we increase that to every 2 minutes during index rebuild (which, for us, is our largest transaction load)If you are using FULL Recovery Model (and assuming you are already backing up the log at 15 minute, or so, intervals) then look at the size of your log backups and see if there is a specific time of day / week when the backups are noticeably bigger - that will point you to a scheduled task that is creating the largest transaction loadIf you have FULL Recovery Model and are backing up the log less frequently than every 15 minutes then that is not often enough! I suppose every hour might be OK, but if you are doing that why not back up every 15 minutes? It represents a shortly period of data-loss time if you have a disaster (but much more frequently than that is probably not worth the effort/cost) |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-02 : 10:12:28
|
| Maybe take a read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-02-08 : 03:49:59
|
| Ok. Thanks for your answers and your time. I'll probably show up here again for complementing questions. Right now I'm trying to avoid the problem until it's get crucial, but sooner or later I'll have to do it. :-) |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-03-20 : 05:39:43
|
| Ok, thanks for the help so far. I've read through the article and google some. I've tried setting a regular backup of the transaction logs, but that doesn't seem to affect the size of the log files. Will that happend directly, or take some time? Should I place the log-backup together (in the same bak-file) with the daily backups of all the databases? No I have them in a separate folder.My attempt was to via the Management/Management plans-folder schedule a backup of all the user databases, every 15 minute. The problem is that it doesn't seem to affect either the size of the transaction logs (in the file explorer) or the date modified for the files. Lots of backup files has been generated though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-03-20 : 09:52:04
|
| LDF files won't shrink. The size does not reduce because it takes time to expand then, and if they were not pre-allocated the system might run out of disk space trying. If the file is excessively large (e.g. because Log backups were not done in the past) then do a one-time manual shrink. (Don;t do it routinely though, repeated Shrink/Grow will just fragment the files and performance will be hit)Don't place multiple backups in a single file. Increases the risk of failure. You can, but I don't - and I can't actually think of circumstances where I would choose to combine them. |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-03-20 : 12:16:09
|
quote:
Originally posted by Kristen
LDF files won't shrink. The size does not reduce because it takes time to expand then, and if they were not pre-allocated the system might run out of disk space trying. If the file is excessively large (e.g. because Log backups were not done in the past) then do a one-time manual shrink. (Don;t do it routinely though, repeated Shrink/Grow will just fragment the files and performance will be hit)Don't place multiple backups in a single file. Increases the risk of failure. You can, but I don't - and I can't actually think of circumstances where I would choose to combine them.
The log backups all go to a separate files for each backup and each database transaction log, and they're set to last for two days before they expire.Ok, so the transaction log won't shrink automaticly. If I do it manually, will it not begin to grow until it once again reaches the same size, as before the log backup rutine? I've read elsewhere that you shouldn't shrink log files. Is it safe to shrink and what's the downside of doin it? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-20 : 12:32:15
|
| You shouldn's shrink regularly. A scheduled shrink is a bad idea. A once-off shrink after some abnormal activity or poor maintenance that's left the log far larger than it needs t be is OK.--Gail ShawSQL Server MVP |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-03-21 : 06:45:26
|
| Thanks a lot! Now I think I've got it working. Had som problems at first with a log file that wouldn't shrink, but it finally worked.I've set the transaction log backups to last for 3 days, so if they've been owerritten on monday, everything should be fine, and I should have lots of space on my disks. (The first transaction log backup was 12 GB!) :-) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-21 : 08:21:32
|
| How often are you running full backups? Bear in mind that to restore to a point in time you need the full backup and then all log backups from the full up to the point you're restoring to.--Gail ShawSQL Server MVP |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-04-04 : 03:12:07
|
quote:
Originally posted by GilaMonster
How often are you running full backups? Bear in mind that to restore to a point in time you need the full backup and then all log backups from the full up to the point you're restoring to.--Gail ShawSQL Server MVP
I'm doing backups every weekday at 20.00. Between those database backups I'm doing backups of the transaction log every 2 hours. I have thought about increasing the frequency of those. Maybe I'll try that later.Right now I have a problem with the expiration date of the backup, both for the full database backup and the log. Both maintenance plans are set so the backups are gathered in the same file. I've set the expiration date to 7 days. Even so they're not overwritten, neither if the backup is set to overwrite or append.What I want is to keep daily backups a week back that will be deleted once they expire. Is that possible? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-04-04 : 13:33:44
|
| First, you should not have your backups (full, diff or trn) being sent to the same file. You should setup your plan to use separate date stamped files and set the maintenance plan to create a directory for each database.Second, the option you have selected is really only valid for tape backups. You need to add the maintenance cleanup task to your full backup plan - and add a separate task to cleanup full backups and one to cleanup transaction log backups. Do not put that task in the same plan as your transaction log backups or you end up with a rolling set of files and will delete files you really want to keep. |
|
|
|
PerS
Starting Member
7 Posts |
Posted - 2012-04-10 : 04:47:32
|
quote:
Originally posted by jeffw8713
First, you should not have your backups (full, diff or trn) being sent to the same file. You should setup your plan to use separate date stamped files and set the maintenance plan to create a directory for each database.Second, the option you have selected is really only valid for tape backups. You need to add the maintenance cleanup task to your full backup plan - and add a separate task to cleanup full backups and one to cleanup transaction log backups. Do not put that task in the same plan as your transaction log backups or you end up with a rolling set of files and will delete files you really want to keep.
Ok, makes sense. I'll try that. Thanks!Btw, should the transaction log backups go to the same folder as the database backup. Does that matter? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-04-12 : 14:41:49
|
quote:
Btw, should the transaction log backups go to the same folder as the database backup. Does that matter?
It can matter, depending on how you setup the maintenance cleanup task. I always put all backup files (full, diff and tran) in the same folder and use a custom utility to remove old backup files. My utility removes the backup files based on the number of versions I want to keep - and makes sure that all related backup files are kept. For example, all related transaction log backups are kept with the full backup and removed when that full backup is removed. |
|
|
|
|
Controlling backup and log size