Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 8 '12 at 4:30

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer
    
Yes, I am worried about the indexes as well. Probably, I will rebuild the indexes as I feel the performance degrade. –  mrp Jan 7 '12 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 '12 at 17:10
add comment

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.

share|improve this answer
    
THank you for the link. It was helpful. –  mrp Jan 7 '12 at 17:04
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.