1

Hey there I'm usually more of a development dba but the old dba left --- and he left about a year ago without a replacement.

I'm finally actually looking under the hood of some of these servers -- I'm not a production dba but I ran some diagnostics I found online and they say I have big problems.

Anyway I'm learning this stuff slowly but this seems like a common issue --- I have Full Recovery mode on a particular database, but never back it up -- as a result the log file grows exponentially -- I take it?

What are my options to get rid of this?

I'm guessing:

  1. If I do a full back up or schedule one, the log file will get cleared after this?

  2. Can I just change to the simple recovery model? I know that has recovery implications - I'm comfortable with data loss as this is a BI database. Will that flush out the log file immediately as well? Do I have to do anything OTHER than a switch to the simple recovery model?

Is there some way to monitor the progress of either of these two options? I'm guessing either will cause the database to start chugging resources doing something or other.

Thanks!

2 Answers 2

1

Do I have to do anything OTHER than a switch to the simple recovery model?

If you have a full backup, and don't need point-in-time recovery, simply switch the database to Simple recovery. After the next Checkpoint you should be able to shrink the log file. If the active VLF happens to be at the end of the file, rebuild an index or two, and try again.

3
  • I have to manually shrink the log file? Is this the same as shrinking other database files? I heard shrinking database files is generally a bad idea from this but not sure: brentozar.com/archive/2009/08/…
    – user45867
    Commented Dec 1, 2020 at 17:15
  • 1
    @user45867 It's generally recommended against and a controversial topic, but there are specific cases where it makes sense and is ok to do. If David Browne is recommending it, then I wouldn't be overly concerned about doing it in this one time instance. Conversely you don't have to shrink the log file, and you won't get the space back it allocated thus far, but at least it won't continue to grow exponentially (and any unused space in it will be available to new transactions).
    – J.D.
    Commented Dec 1, 2020 at 17:52
  • Right that makes sense. Read you loud and clear!
    – user45867
    Commented Dec 1, 2020 at 18:03
1

Recovery mode allows for Point In Time recoverability. If you are in SIMPLE mode, then you can only restore back to your last FULL or DIFFERENTIAL Backup. If you are FULL, then you can recover back to a specific point in time based on the log backups you have taken.

Transaction log backups should be taken to meet a particular Recovery Point Objective (RPO). If the Business says that they will tolerate 1 hour's worth of data loss in a disaster, then you need to take transaction log backups at a minimum of hourly. These transaction log backups will be used in a recovery situation in conjunction with your FULL and DIFFERENTIAL backups. Taking transaction log backups will also keep your transaction log file in the database from growing exponentially.

If you do not have a backup strategy, look at this article by Paul Randal. https://www.sqlskills.com/blogs/paul/planning-a-backup-strategy-where-to-start/ https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-8-of-30-backups-planning-a-recovery-strategy/

Once you have some understanding about what you should be doing, look at this site for a set of scripts that will automate the backup and maintenance of your databases. https://ola.hallengren.com/

Ola Hallengren has written a stellar set of scripts that most DBAs will use to set up backups of their environments. They are super simple, customizable, and powerful.

3
  • Thanks, I will go ahead and power through that. I suppose I should read before doing anything. But say I can tolerate a week or two of data loss (I can reload with ETL scripts, it's a data warehouse) -- we have VSS server backups in case of dire emergency, and currently the database has no backups ever. --- Would switching to a simple recovery at least free up storage/ prevent further log file growth?
    – user45867
    Commented Dec 1, 2020 at 17:17
  • Switching to SIMPLE recovery mode will not free up the space on disk, only in the file itself. After you switch the recovery mode and need to reclaim disk space, you can shrink the file inside SSMS via UI or by using the DBCC SHRINKFILE command.
    – user103243
    Commented Dec 1, 2020 at 18:19
  • Makes sense -- looks like in most guides warning against shrinking database files, they do point out an out-of-control transaction log file as an exception to go ahead and shrink. I'll go ahead and try it. Thanks
    – user45867
    Commented Dec 1, 2020 at 19:07

Your Answer

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

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