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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Sql 2005 log files fill up

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 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

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
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -