Avatar of rocky_lotus_newbie
rocky_lotus_newbieFlag for India

asked on 

SQL Log files Maintenance

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
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
rocky_lotus_newbie
Avatar of spiderwilk007
spiderwilk007
Flag of United States of America image

Setup an maintenance plan to do a compact and repair each night after the backup is done. This should reduce log and database size.
Avatar of spiderwilk007
spiderwilk007
Flag of United States of America image

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
Avatar of rocky_lotus_newbie

ASKER

Typo: The total size of the log files of the databases is around 120 GB
Avatar of Anthony Perkins
>>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.
Avatar of spiderwilk007
spiderwilk007
Flag of United States of America image

Any luck setting up a maintenance plan?
Avatar of rocky_lotus_newbie

ASKER

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.
Avatar of rocky_lotus_newbie

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rocky_lotus_newbie

ASKER

Thanks acperkins.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo