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.

Background:

I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are set to FULL recovery and the transaction logs have never been backed up. I've gone through all of the SQL Servers and identified low priority ones that only need a nightly backup and a where a day of data loss is acceptable.

Question:

I'm switching a lot of low priority databases to SIMPLE recovery mode from FULL. Will the existing transaction logs be truncated (when checkpoints are created)? Some of the existing transaction logs are 50-100GBs; what is the best approach in determining what I should shrink them down to for the purposes of moving forward? I obviously don't want to keep them that large. Or, will they shrink on their own over time (I don't think they will)?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Before switching from FULL to SIMPLE recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE should be OK. If this is not the case, stay with FULL

To shrink the LDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database
  2. Run in SSMS

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)

  3. Modify the transaction log file size

    ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)

share|improve this answer
1  
I would not suggest to SHRINK Log files as it will cause VLF fragmentation and the entire database workload will be paused while the log grows, as transaction log file can't use instant initialization. –  Kin Jul 22 '13 at 21:23

I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)?

In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval (advanced server config setting) or if the log becomes 70% full.

Unless you have some long running transactions occuring that will delay log truncation, an automatic checkpoint will truncate the unused portion of the T-log.

Some of the existing transaction logs are 50-100GBs, what is the best approach in determining what I should shrink them down to for the purposes of moving forward. I obviously don't want to keep them that large.

If you have the database recovery model set to FULL for those database with 50-100GBs of T-logs, then you have to start doing frequent T-log backups. Remember in Full recovery model, once a log backup chain has been established, even the automatic checkpoints wont cause the log to truncate.

As a last resort, you can truncate the log file and then immediately take a full backup and then start taking T-log backups so that you can do point-in-time recovery if a disaster happens.

Or, will they shrink on their own over time(I don't think they will)?

As @TomTom pointed out, its a manual operation.

Read up :

share|improve this answer

Many of the question s can not be answered by us. How long is a piece of string?

Some of the existing transaction logs are 50-100GBs, what is the best approach in determining what I should shrink them down to for the purposes of moving forward.

As long as they need to be. I suggest NO shrinking. Trunacate logs, come back in a week and see how much space is used, THEN decide. But YOU have to answer this one.

The nightly backups are roughly 8TB and needless to say, we're using more disk space than we'd like.

So why turn them to simple? I mean, seriously.

All of the databases are set to FULL recovery and the transaction logs have never been backed up.

A little logic will tell you that igf you truncate them ONCE, then you will likely use a LOT less space for backing them up anyway. The result may be that you can well keep them in full recovery mode. Try that out first. If they rally are low volume etc. then the log backups wil lbe a LOT smaller in the future.

I've gone through all of the SQL Servers and identified LOW priority ones that only need a nightly backup and a days worth of data loss in the even of a disaster will not be a problem (Fax databases and things like that).

Yes. That is until you end up in court and get your ass handed for not having critical legal documents. Do you know that the fax logs may well be part of what you are required to keep for years as business relevant information? It is like that in my jurisdiction (10 years). If you are a U stock company there may be similar surprised (SOX). Failure to do so makes it VERY bad in court if you want to prove you did not get a fax. Or did send one. No one cares whether this happened monthly back and you have more recent logs - you fail the law requirements. Make sure this is signed off by someone VERY high, because your business not critical may be your firing reason.

Or, will they shrink on their own over time(I don't think they will)?

No. And they should not do that. Log re-sizing is a manual operation except for low volume databases.

share|improve this answer
    
Thanks for the feedback. I agree with the first point and will keep an eye on them. Setting them to simple so as to not have to worry about log maint. and we have no need to retain them. Staying in FULL recovery and beginning to take log backups (even if they are small) still is additional space that we do not have. The designation of LOW priority SQL Servers was a business decision made above me. –  BamBamBeano Jun 14 '13 at 15:59

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.