3

I have recently taken over a system which is going live in March on SQL Server 2014 after running on Sybase for years. The system is 24 x 7 OLTP and heavy batch at night. Prior years during batch in Sybase the transaction logs would fill up freezing the system. I have not managed a system such as this and was looking for some advice.

I would propose sizing the transaction log appropriately and running log backups every 15 minutes. Does this sound as though it would be the best procedure? Also, how should I best size the log? Should it be 2 x the largest transaction size? What if the size of the transaction varies?

|improve this question
  • 2
    What were the results in the test environment? – Tom V - Team Monica Jan 30 '16 at 16:43
  • Can you add relevant info such as: software and hardware use, current settings, etc. – Julien Vavasseur Jan 30 '16 at 16:58
  • To determine how frequently to run the log file backups, please provide answers to: How much data loss is acceptable? How long can the system be offline? (It's more about data loss and availability than anything else.) – kendra Jan 31 '16 at 21:42
2

Prior years during batch in Sybase the transaction logs would fill up freezing the system.

Sybase before ASE 15 did not have the concept of autogrowth. SQL server has autogrowth and there are ways wherein you can presize your transaction log and choose a sensible autogrowth value.

Refer to : 8 Steps to better Transaction Log throughput and Manage the Size of the Transaction Log File

Also, Why Does the Transaction Log Keep Growing or Run Out of Space? has excellent answers detailing majority of techniques and scenarios.

I would propose sizing the transaction log appropriately and running log backups every 15 minutes.

Initial sizing would be a trial and error method, but you can use my script to detect autogrowth events and then adjust your log size.

Running log backups will allow you to reuse your existing log file in FULL and bulk logged recovery modes.

A checkpoint (manual or automatic) will clear the log file in simple recovery.

heavy batch at night.

You should refer to data loading performance guide (Referenced in my answer) to optimize your nightly load batch. Also, you should check Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads

|improve this answer

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