Rebuilding, truncating etc SQL Server 2008 maintenance

Question

About a month or so ago I inherited this somewhat-new database. I was lead to believe that everything was set up and all maintenance tasks were running without a hitch. SQL Server 2008 R2

I was looking at it today while trying to troubleshoot some slowness and discovered that we are almost out of space on our ~500gb array. We have multiple tables whose sizes are upwards of 6gb, which I'm not too concerned about. BUT they have corresponding log files that are around 5x the size! One of them is 31gb. Their modification dates are 3 days ago. The little I have read leads me to believe this is incorrect.

My questions-

1. Can I safely truncate these log files?
2. How often should I do this, and when? (The nightly backup is at 12am.)
3. I see no re-indexing, statistic updating, etc. Is it safe to go ahead and set these tasks up? Should I wait until after the aformentioned truncation?
4. Can the software writing/reading to the database be used while these tasks are being performed? We are 24 hours and stopping production is very difficult.
5. We also have a LOT of backup files. Like, one for every table for every day since February. Can I delete some of them? Is there a way to set the backup to do this automatically?

Thanks so much guys. We have an IT department but I'd rather not have to wait on them, especially if these things are easy to do. I'd rather set them up myself (if possible) and let a DBA check in on them occasionally.

Answer 1

Your questions cover a lot of scope for SQL Server database maintenance. We need to address two areas here: Backups and Index Maintenance.

Backups

The first thing you must understand before you can properly manage your backups is your recovery model. This will determine how your transaction log is managed by the engine. The simple description (the "real answer" is far more involved) is that your log file contains the records of each transaction that has occurred in your database. In relation to that, your recovery model tells the engine whether or not you want point in time recovery (Full/Bulk Logged if you do, Simple if you don't). Based on this information, the engine will remove records from the log file at checkpoints if it considers them safe to remove:

• Full/Bulk Logged: Since point in time recovery is desired, log records will not be removed until they have been safely backed up. The engine needs to ensure that these log records can be made available for recovery. Note, this is different than a full database backup. If you need to restore your database, you will require both a full database backup and attendant log backups. More detail on this restore process can be found here.
• Simple Recovery: Log records are removed if transactions are completed. Since point in time recovery is not desired, the engine will not retain the log records.

In regards to your other concerns, the backup operations are done online, so they won't directly impact your database. Full backups capture a point in time, so any previous files will represent a previous point in time and, if you don't need those previous points in time, you won't need to retain those older backup files.

As for a backup schedule(frequency and retention of files), this is very much of an "it depends" answer. It is discussed in more detail here.

Index Maintenance

This is a MUCH deeper topic, but essentially what you're looking to do is manage your index fragmentation so that it does not impact performance. There's a lot written out there on what it is and how to manage it. ToadWorld is a good place to start on how to manage your indexes and what index maintenance means. This can impact use of your database (though the database will remain online for the operation), but there's lots of different factors around this impact and how to manage it.

Maintenance Plan

If you have DBA resources in your company, these tasks are within their bailiwick and they will likely have tools and a roadmap for managing these already. If you don't have these resources, there are several tools that can be leveraged. For maintenance as a whole, Ola Hallengren provides some free tools for this that are highly regarded and recommended in the SQL community.

Answer 2

The first thing you should check is, why the log files are growing:

SELECT name,log_reuse_wait_desc FROM sys.databases;

You can find an explanation of this query here: http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/

There are also a few links to additional information in there.

The most likely cause in your case is, that you have not executed any log-backups. If you don't need log backups you can set the database to recovery model simple. That would allow you to release the unused space in the log file and keep it small afterwards. However, in general it is not recommended to set a production database that contains data that is worth anything to you to "simple". You can read about the drawbacks here: http://msdn.microsoft.com/en-us/library/ms191164(v=sql.105).aspx and compare it to "Full" here: http://msdn.microsoft.com/en-us/library/ms190217(v=sql.105).aspx