3

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.

Improve this question
0

4 Answers 4

Reset to default
1

Given the drive the file is on has enough contiguous freespace just recreate the file, or stop the dbms, a defrag it. After that it shouldn't be a problem.

You aren't using shrink are you?

If it was me I'd be more worried about my indexes being a mess that the transaction files. Some sort of drop/create / rebuild of them would be something I'd look at.

Improve this answer
2
  • Yes, I am worried about the indexes as well. Probably, I will rebuild the indexes as I feel the performance degrade.
    – mrp
    Jan 7, 2012 at 17:04
  • @mrp You need to really reconsider your indexes that you have on that table. I'm guessing there is some OLAP on it, but if the majority of activity are INSERT and DELETE, then indexes add quite a bit of overhead to data manipulation.
    – Thomas Stringer
    Jan 7, 2012 at 17:10
4

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:

  1. The maximum amount of data that can be lost in the case of a disaster
  2. 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.

Improve this answer
0
1

You need to set proper transaction log backups. During this operation the log file gets trimmed. Start by identifying the maximum amount of data your business can afford to loose. For example if you can afford to loose 1 hour of data, you can have full backup every 24 hours, Differential every 4 hours and Tran Log every hour. Here's the MSDN link about Transaction Log Backups: http://msdn.microsoft.com/en-us/library/ms190440.aspx

If you can afford to loose even more (and don't need to restore to a particular moment in time, but just to the last backup), consider switching from Full to Simple database recovery model. In this case you'd do just Full and Differential backups greatly simplifying the maintenance. Read more about recovery models: http://msdn.microsoft.com/en-us/library/ms189275.aspx

And of course in your case you should configure rebuilding indexes BEFORE you start experiencing performance problems.

Improve this answer
0

You can setup scheduled maintenance tasks to manage the fragmentation of your database.

If older records are no longer required you could also consider migrating them regularly to an OLAP database/data warehouse such that your OLTP tables and indexes are regularly trimmed

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.