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
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
Last Comment
spiderwilk007
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
Backup Full=>Check Database Integrity=>Update Statistics=>Cleanup History=>Re-Organize Index=>Rebuild Index=Shrink Database=Mainenance Cleanup task
ASKER
Typo: The total size of the log files of the databases is around 120 GB
>>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.
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?
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.
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.
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
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.
ASKER
Thanks acperkins.
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
TRUSTED BY
