Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
10Dawg
Starting Member
46 Posts |
 Posted - 2013-12-16 : 15:36:31
|
| Not to beat a dead horse but I've been reading on this subjuct for about an hour now and it's like reading about losing weight, there's as much mis-information as not. So here goes.... I have a sql 2005 that needs a full back up once a week to satify the powers that be. But the drive that holds the log files sometimes fills up. I check and some log files are 2-5 times larger than the mdf. From what I've read, the best way to manage the log file size on a regular basis is: 1- Full backup, 2- Trans Backup, 3- DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) One after the other, in that order. OK bomb away!!!! But if I'm anywhere in the ballpark, Do you have to do step 3 for every db? I've got 760 in this environment and am not informed when they come and go. Is there a universal command? Thanks in advance.10Dawg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-16 : 16:16:39
|
quote:
From what I've read, the best way to manage the log file size on a regular basis is: 1- Full backup, 2- Trans Backup, 3- DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) One after the other, in that order.
No. You do not shrink the file and you especially do not truncate it as that breaks the tlog chain. Do not shrink the files unless you know it won't grow again. Growing the files is an expensive operation, especially for the log file as you can't use instant file initialization for it. Here is our backup plan:1. Full backup nightly2. Diff backup 12 hours after the full3. Log backups every 15 minutesTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
10Dawg
Starting Member
46 Posts |
Posted - 2013-12-16 : 16:51:01
|
| Whoops, sorry I forgot to mention that the once a week FULL backup on ALL databases decreed by the higher-ups is way-over-kill. I don't even want the Log files and will delete them almost immediately. Look, I just want a Full backup once a week and I want my log files to go back to a more managable size so the log drie doesn't fill up. I have 750 dbs, only 5% of the databases get used on a weekly basis so the Diffs and Trans are not necessary on any but maybe a few of the new ones. Does that change things at all?10Dawg |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-16 : 17:09:31
|
| For any database which you (and your business folks) don't mind losing DML/DDL changes since the last full backup you could change the recovery mode to SIMPLE. That way the t-log is truncated automatically at every checkpoint. That would likely solve your space problems but I don't know any companies that knowingly do that for their production environments. I've heard of some DBAs getting fired for doing that though.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-16 : 17:41:44
|
| I agree with TG that you just need to change your recovery model to SIMPLE given the info you've provided. Using full/bulk_logged and performing log backups regularly is only needed where you care about point-in-time recovery. You don't seem to care about that.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Sql 2005 log files fill up