0

As per the title, how much is it safe to use BACKUP LOG db_name WITH TRUNCATE_ONLY in last STEP of any SQL-job execution? Randomly found on google that this Code line can be very harmful in use. But how?

I am using my code as below in one of my SQL-job's last STEP.

BACKUP LOG  my_database with Truncate_Only
DBCC SHRINKFILE (N'my_database_Data')
DBCC SHRINKFILE (N'my_database_Log')

Let me simplify the question with below aspects:

  1. What actually doing this code in the SQL-Job? Does it really help?
  2. Is there anything related performance of the DB-Server?
  3. If I remove first line of code (BACKUP LOG), how it can get effect?
  4. Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

This could be opinion based question but still let's discuss if you can share some Ideas on it.

  • 1
    I'm not sure if this will even work - truncate_only was removed in 2008 as far as I'm aware? The only log specific backup options I can find are no_truncate and copy_only. – George.Palacios Jan 24 '19 at 8:48
  • @George.Palacios: Yes true, But i think it's remove only from 2008, there new versions still using this functionality, I am using SQL2012 currently. – Irfan Jan 24 '19 at 9:03
  • 1
    No, the TRUNCATE_ONLY option was removed in 2008 and it is also not present in more recent version. So, you cannot use it nowadays. It is essentially the same thing as doing "BACKUP LOG dbname TO DISK = 'nul'". MS found it was being mis-used, breaking the log backup chain causing disaster if you later have to restore from a chain of log backups and now that chain is broken. That is why this option was removed. – Tibor Karaszi Jan 24 '19 at 9:15
  • 1
    Upvoted George.Palacios comment. This code fails on 2012 with the error 'Truncate_Only' is not a recognized BACKUP option. – sepupic Jan 24 '19 at 14:58
2
  1. What actually doing this code in the SQL-Job? Does it really help?

SQL is backing up the log to nowhere and then trying to shrink the data and log files of your database. All of these are bad ideas. Backing up the log to nowhere means you lose point-in-time recovery of your database because you cannot restore these portions of the log. See this link for more info.

  1. Is there anything related performance of the DB-Server?

Constantly shrinking your log and data files can cause performance issues because SQL Server has to regrow these files as needed, which causes waits on your queries that require the additional space.

  1. If I remove first line of code (BACKUP LOG), how it can get effect?

Before you can reclaim space in the log, you need to backup the data in the log. Removing this would mean your shrinking wouldn't likely free much space, however, as stated before shrinking the log is a bad idea anyway.

  1. Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

No, this is unrelated. Most likely your SQL Agent history is limited and you have a high-frequency job filling the job history tables. See this link.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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