1

Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery mode and the transaction log has never been backed up.

I intend to run the transaction log backup every 15 minutes but I am afraid that the first backup may take a very very long time (hundreds of GBs of data).

What is the right course of action here?

I'm a developer by training and not a DBA so I would appreciate a more detailed answer. Thanks in advance.

2 Answers2

0

I would take the first transaction log backup by hand (not as a scheduled task) and during hours when a performance hit will be acceptable to your users.

Then take a second immediately so that the working area of the log file moves back to the start of the file.

Then shrink the log file.

Then start your scheduled transaction log backups.

Jonathan Fite
  • 9,414
  • 1
  • 25
  • 30
-1

Full recovery model keeps all transactions in log file till backup log happens.

Here are your steps

  1. Backup the Database (just in case)

  2. Change recovery model to simple

  3. Truncate log file

  4. Change recovery model back to full

marc_s
  • 9,052
  • 6
  • 46
  • 52
nsb
  • 101
  • 2