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.

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.

share|improve this question
    
You mention "tables" frequently, but the context implies you mean databases. E.g. databases have free space, not tables. You take backups of databases not tables, etc. –  StrayCatDBA Apr 24 '13 at 11:20
1  
Let the DBA do these DBA tasks... (just my opinion) –  Thomas Stringer Apr 24 '13 at 13:42
    
Thanks for the jargon correction. And I will definitely let a DBA do any non-urgent maintenance, but right now I need to reclaim some space. Thanks for the feedback. –  posternutbag Apr 25 '13 at 8:12
add comment

migrated from stackoverflow.com Apr 24 '13 at 10:55

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

2 Answers

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.

share|improve this answer
    
Thanks. Great, helpful answers! Sounds like I can wait on an expert for the rebuilding indexes and other maintenance. I do however need to recover some space pretty soon. We are set to do full backups every evening. I guess I need to set up some log backups so they start truncating themselves? And it seems there was no expiration date set on the backups, so we have daily ones going back 2 months. If I change the backup task to have a 7 day expiration, will it automatically delete the old backups? They aren't cumulative so there isn't a reason to keep them for so long, right? –  posternutbag Apr 25 '13 at 8:15
    
Expiration dates will not delete files, you will need some sort of file system job to manage this. There are many options which you can leverage for this. –  Mike Fal Apr 25 '13 at 14:25
add comment

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

share|improve this answer
    
Thanks Sebastian. Looks like you are correct- no log backups. Will the backups of these log files be as huge as the logs themselves? If so, I guess I need to do them on an external drive until they shrink to a reasonable size. –  posternutbag Apr 25 '13 at 8:17
add comment

Your Answer

 
discard

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

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