1

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.

  • You say the logs are needed for replication. What type of replication are you using? – James Anderson Jul 8 '14 at 11:49
  • They're part of an availability group using synchronous commit. – Mourndark Jul 8 '14 at 12:04
  • Setting backups to expire doesn't cause them to be deleted. You need to add a task to your job which deletes the backups. It's the "Maintenance Cleanup Task" which has the option to delete the files. – mrdenny Jul 8 '14 at 16:22
  • Thanks for the tip, I spotted that task and wondered what it was for! – Mourndark Jul 10 '14 at 9:57
0

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.

Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?

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.

  • Thanks, really useful answer. I've upped the backup frequency to be on the safe side. I've just tried shrinking the transaction logs, but for some reason only one of the DBs and its log file are showing up in sys.database_files. But the shrinking seems to have worked on that one! – Mourndark Jul 8 '14 at 13:41
  • The missing files are showing up in sys.sysaltfiles and sys.master_files on both nodes, so neither node is able to run DBCC on them, any ideas? – Mourndark Jul 8 '14 at 14:23
  • What do you mean, "missing files"? – Thomas Stringer Jul 8 '14 at 14:24
  • Sorry, all the backup files are showing up in the file system, but only one file appears in sys.database_files. The other backups only appear in sys.sysaltfiles and sys.master_files - so I can't run DBCC on them. – Mourndark Jul 8 '14 at 14:28
  • Ah, my mistake, I missed that DBCC should be run on the database in question. Need to add some USE statements to my query! – Mourndark Jul 8 '14 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.