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.

  • Aaron, I read that post before but it doesn't quite answer my concern. My concern is that the first log backup may take a very long time and I may not have even enough space for the backup. What is wrong with the answer given by nsb? –  Aug 08 '14 at 16:18
  • 4
    This would be closed as duplicate if it's migrated to DBA. – billinkc Aug 08 '14 at 16:20
  • 4
    Take another full backup before you start taking log backups. This will negate the need to go back in time and take a log backup of all history. If you need to make the log file small again, perform that as a separate activity after you have set the database up properly. – Aaron Bertrand Aug 08 '14 at 16:28
  • 4
    @user3922913 do you need full recovery model? –  Aug 08 '14 at 19:54

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
  • 8,666
  • 1
  • 23
  • 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
  • 8,932
  • 6
  • 45
  • 51
nsb
  • 101
  • 2
  • 4
    Truncate log isn't even supported anymore (think it was dropped in 2008). Also this messes with the log chain and you haven't given any advice about re-establishing a new backup chain. This sequence of steps might be ok for your baseball card collection database on your brother's laptop but is hardly the advice I'd give to anyone running SQL Server in production. – Aaron Bertrand Aug 08 '14 at 16:16