2

I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen).

The recovery mode is FULL (SQL Server 2005).

This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?

closed as primarily opinion-based by Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Paul White, Marian Sep 9 '14 at 14:46

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Are you asking how much time would transaction log backup take or how much time does log truncation takes ? Edit your question accordingly – Shanky Sep 8 '14 at 10:28
  • 1
    So you don't have any full or log backups? – Marian Sep 8 '14 at 12:03
  • 2
    Ha, just found this gem. Probably worth reading all the question, comments, answers. – Marian Sep 8 '14 at 13:09
5

but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen). The recovery mode is FULL (SQL Server 2005).

I guess previous users were taking file system backup which in my opinion is not best practice when you have important app running. You can never get point in time recovery. Your thinking is correct about creating a maintenance plan backup it gives you more control on backup. Plus if you don't want point in time recovery you can put database in simple recovery mode and can just have full and differential backups as per RPO and RTO. This would avoid the hassle of transaction log file backup and unnecessary growth of log file

This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take. Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?

First please note log truncations and transaction log backup are not same thing.Its transaction log backup only which is responsible for log truncations in full recovery mode. For DB in simple recovery mode checkpoint causes log truncation or when log grows 70 % of its size.

Regarding how much time transaction log backup of 60 GB log file would take its hard to predict there is not such formula. If you have faster disk where log file resides and faster disk where you are dumping the backup it would take normal time. Backup reads data from disk and writes it to disk so if disk is not facing I/O contention it would be quick.

You must also read

Optimizing backup and restore Performance in SQL Server

Understanding SQL Server Backup

3

Ball park - it will have to read 60Gb out of the log file, write 60Gb to the backup file, do some metadata operations for the log truncation itself and (optionally) move what's left of the log file if you shrink the ldf. For your average disk subsystem I'd expect you would be done and dusted in couple of hours, tops.

  • 1
    Good approximation, but still, OP says they're using FULL recovery model but with no FULL or LOG backups. In this case I'd say a switch to simple recovery and then shrink with truncate will suffice. And I'd try to plan for a sensible growth for the log file (if there's any past info). For my safety, I'd just configure at least a full backup plan and change the db to be in simple recovery model. But this needs to be planned carefully with the site owner (SLA..etc). – Marian Sep 8 '14 at 12:04
0

Well, if you are relying on the physical file backups, that is okay, you can take a transaction log backup and then shrink the 'log' file to a small amount and change the recovery model to simple because you are not planning to take log backups.

However, to make the approach more sound, schedule the full and regular T-log backups so that you can restore point in time and keep the log file size intact and when you need to restore you have power to do so rather than asking a sysadmin for physical file backups(let it be there additionally)

Thanks Chandan

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