What is the optimal set of database maintenance tasks for high volume of transactions

I have a database that has a high volume of data being inserted and deleted each week.

Every week there are approximately one million rows of data being deleted and inserted. Each month the base table on which the data gets inserted grows by around a million rows.

I think this large number of transactions are causing the log file to grow larger and get fragmented.

I am afraid the performance may get worse each month.

What is the best way to maintain and manage such a database?

If anyone can explain or provide a link that deals with such a scenario that would be great.

As far as the transaction log goes, I highly recommend you read this article: SQL Server Central: Managing Transaction Logs. The biggest takeaway you should get from this is that you need to plan your transaction log backups according to:

• The maximum amount of data that can be lost in the case of a disaster

• The size to which the log can grow.

The above was quoted directly from the above article on SSC.

What #1 is indicating is that if the drive that your log file exists on is corrupted and you can't salvage your log file (which means you can't back up the tail of the log for the minimization of data loss), then you will have data loss since your last transaction log backup.

And #2 being obvious, the transaction log in full recovery mode will grow until backed up.

StackExchange Database Administrators Q#10342, answer score: 4