SQL Log files Maintenance

rocky_lotus_newbie
rocky_lotus_newbie used Ask the Experts™
on
Hello,

We have a sql instance (used by development and testing teams) that's on a test server and it has 28 databases (the size of all the databases put together is about 350 GB). All of the databases are in simple recovery mode. We have transactional replication on this instance and it has one publication. It is also a subscriber for 3 subscriptions.
We take full backups of all the databases every night. At this point, we don't have any sql job that truncates/ shrinks the log files of the databases and as a result the size of the log files of all the databases put together is about 60 GB. What's the best way to manage the log files and also reduce the log space taken?
SQL Version is : 2008 SP2
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Setup an maintenance plan to do a compact and repair each night after the backup is done. This should reduce log and database size.
My Mainenance Plan is like this:

Backup Full=>Check Database Integrity=>Update Statistics=>Cleanup History=>Re-Organize Index=>Rebuild Index=Shrink Database=Mainenance Cleanup task

Author

Commented:
Typo: The total size of the log files of the databases is around 120 GB
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012

Commented:
>>we don't have any sql job that truncates/ shrinks the log files of the databases <<
Nor should you.  

>>as a result the size of the log files of all the databases put together is about 60 GB.<<
Why is that a problem?  That sounds reasonable to me.
Any luck setting up a maintenance plan?

Author

Commented:
Thanks spiderwilk007 for your inputs. We are not making use of the maintenance plans at the moment (and we do not plan to use) and all the maintenance tasks have been custom written.

Author

Commented:
acperkins,

We are trying to add new data to the existing data , so I am looking to free as much disk space as possible. Would there by any impact on Transactional replication if at all we shrink the publisher/ subscriber databases? Just trying to explore a few options...Thanks for your time.
Top Expert 2012
Commented:
>>Would there by any impact on Transactional replication if at all we shrink the publisher/ subscriber databases? <<
You should not shrink the database unless it is an emergency (you are running out of disk space).  Shrinking the database frequently will only cause fragmentation.

Author

Commented:
Thanks acperkins.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial