SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Sql 2005 log files fill up
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

10Dawg
Starting Member

USA
46 Posts

Posted - 12/16/2013 :  15:36:31  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 12/16/2013 :  16:16:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 nightly
2. Diff backup 12 hours after the full
3. Log backups every 15 minutes

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

10Dawg
Starting Member

USA
46 Posts

Posted - 12/16/2013 :  16:51:01  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/16/2013 :  17:09:31  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/16/2013 :  17:41:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 12/16/2013 17:41:57
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000