0

I have issues with transaction logs on SQL Server 2012. I set up a job that does an automatic cleanup of logs, I used the following the plans below.

Job name: Cleanup DB Logs

Step 1: Do transaction log backup using Management wizard & go to "Step 2"

GO
BACKUP LOG [XXXX_Database] TO  DISK = N'I:\XXX_Database.Trn' WITH NOFORMAT, NOINIT,  NAME = N'XXX_Database-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Step 2: Perform shrink log & transaction log backup and exit upon success e.g. here is the step 2 script below:

DBCC SHRINKFILE (N'CAR2_Analysis_Database_log' , 20)
GO
BACKUP LOG [XXXX_Database] TO  DISK = N'I:\XXX_Database.Trn' WITH NOFORMAT, NOINIT,  NAME = N'XXX_Database-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
DBCC SHRINKFILE (N'XXX_Database_log' , 20)
GO

Then I schedule the job to run every 3 hours daily. The idea of the above script is to force truncation. It works fine, however, after 2 days I see that the STEP 2 disappears from the Job. My question why does this happen? Any pointer would be highly appreciated.

TZHX
  • 5,291
  • 15
  • 47
  • 56
Smartiot
  • 1
  • 1
  • 1
    Is your DBA removing it in anger at you doing such a thing? – TZHX Mar 28 '18 at 06:42
  • No the DBA is not removing it. I already checked. DBA is not sure why it is removed too. – Smartiot Mar 29 '18 at 07:48
  • what are you trying to achieve by doing this? are you actually using transaction log backups as part of your DR setup? constantly shrinking the file is a bad idea, as then it'll just have to grow again. if you're simply trying to avoid the pain of a growing transaction log that you don't care about, put your database into "Simple" recovery model. – TZHX Mar 29 '18 at 07:52

0 Answers0