Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance!
I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB servers. It's only been up for a week or so and my transaction logs have already filled the 25GB allocated for them on each box. This is only a test environment, so I don't really need to keep the transaction logs (normally I just set the logging to "Simple" and let nature take its course) but obviously the logs are needed to ensure everything is replicated properly. What I'd like is for the logs to be dumped as soon as they are no longer needed, but I'm not sure how to accomplish this (or if it's even possible).
Based on my current (limited) understanding, I've set up a Maintenance Plan using the wizard that runs every day to backup the transaction log (so SQL Server will allow it to be deleted). I've set the backups to expire after a couple of days so that hopefully they don't grow too large. Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?
I'm not expecting a complete solution (but that would be nice!) but any guidance as to whether I'm on the right lines would be greatly appreciated.
Best Answer
You are on the right track. With the database in full recovery mode, no portions of the log file will be marked for reuse until it has been backed up. I'm assuming you've created your maintenance plan to have the capability to backup the transaction log. In which case, that should get you to a point where you should be allowing the reuse of log portions (there are other things that can prevent log reuse as well, but not worth mentioning until/unless you run into them).
How often are your transaction logs getting backed up? Depending on the frequency of logged operations, you might need to set that to a more frequent schedule than once a day (again, it depends solely on the rate of logged transactions).
As for the physical log file sizes (25 GB NTFS files), they will not shrink just because you have done the transaction log backups. Backups will not affect the file size. You'd have to explicitly shrink the transaction log, if you in fact need to reclaim that space.
As for "steps to shrink the databases", no you shouldn't have to routinely do this. You may want to do this once to get the log files to management sizes, but then with a well-chosen log backup schedule (see above) those log files shouldn't grow out of control again. And with the "cleanup" of the transaction log backups, that's really up to you. I'm not sure what type of disaster recovery plan you have for this environment, so the retention period of those transaction log backups will be driven by your requirements and the DR plan for these databases.