• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

Will this prevent logs from filling-up?

Will this work?  :

9am:
BACKUP DATABASE [curtis_llc] TO [curtis_llc] WITH  INIT ,  NOUNLOAD ,  NAME = N'curtis_llc database',  SKIP ,  STATS = 10,  NOFORMAT

Hourly during the business day:
BACKUP LOG [curtis_llc] TO  [curtis_llc]   WITH  NOINIT ,  NOUNLOAD ,  NAME = N'curtis_llc backup LOG',  SKIP ,  STATS = 10,  DESCRIPTION = N'LLC Log',  NOFORMAT

I know this prevents my backup device from filling up since it's initialized each morning at 9am, but will this also prevent the transaction logs from filling up?

In other words, does the 9am backup truncate the logs based on the command above?

Should the hourly log backups be init or noinit?

Sorry for the total-newbie-question,

Thanks,
Mike
0
mike2401
Asked:
mike2401
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
Hi,

Depending on your database which SQL Server version? Which Recovery Mode?

If your database is under SIMPLE recovery mode, log truncation occurs automatically after you back-up database.
If your database is under FULL recovery mode, log truncation occurs after you back-up transaction log except you specify WITH NO_TRUNCATE or COPY_ONLY. These option prevent log truncation.

However, log truncation sometimes is delayed by some reasons. Please refer the link
0
 
jogosCommented:
The INIT/NOINIT are for the backup medium/file, not for the logs of database.

The medium of the full backup and log backup is different so there is never an INIT-parameter for the log-backup. So that still continues to build up.
0
 
mike2401Author Commented:
What do you mean jogos, this log backup contains a noinit/init param and does not error out:

BACKUP LOG [curtis_llc] TO  [curtis_llc]   WITH  NOINIT ,  NOUNLOAD ,  NAME = N'curtis_llc backup LOG',  SKIP ,  STATS = 10,  DESCRIPTION = N'LLC Log',  NOFORMAT
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
mike2401Author Commented:
Thank you @Dung Dinh , one server is Sql Server 2000 (Microsoft SQL Server  2000 - 8.00.760 ) )with databases set to Full.  

The other database is Sql Server 2008 with databases set to Full.  The 2008 server has databases running in compatibility level=  "sql server 2000".  The examples in my post were from the sql server 2008 server.

However, what started this investigation was what happened on my sql server 2000 server running our helpdesk database encountered this error:  -2147217900 The log file for database 'helpdesk' is full. Backup the transaction log for the database to fee up some log space.  "

I checked and this was running hourly:
BACKUP LOG [Helpdesk] TO [Helpdesk] WITH NOUNLOAD ,  NAME = N'Helpdesk Log',  NOSKIP ,  STATS = 10,  NOFORMAT

Any reason why this could ever fill up?  The helpdesk database is allocated 184 meg .  Its log is allocated 143 megs (though I swear yesterday the log was allocated for 1000 MB.  ) Being a novice, I added a 2nd log file (that got around the production problem.  Then I did a manual log backup to truncate, then I was able to delete the 2nd log.

Any thoughts would be appreciated.

Thanks,
Mike
0
 
QlemoC++ DeveloperCommented:
Since your backup target location is the same, the DB INIT/LOG NOINIT combination works fine. Transaction log backups will be appended to the DB backup, and that one is created again each day, so your assumptions about backup file growth are correct.

The transaction log should not fill up (grow endlessly). The log backup is ok and frequent. But having a log of 143 megs and a "log full message" leads to the conclusion that your disk did not allow growth, and in addition something is shrinking the log file - which should not be done regularly.
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
Hi,

Is Transaction log backup run through Database Maintenance Plan? Is there any process that is run at back-up time?  It's was very strange because you said that you did it manually and log file was truncated. There were some reasons to prevent log truncation.

You could change your database recovery mode to SIMPLE after backup log and the switch to FULL.

Thanks,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In other words, does the 9am backup truncate the logs based on the command above?
Each transaction log backup will truncate the log file. Keep in mind that truncate the log is not the same that shrink the log file.

Should the hourly log backups be init or noinit?
Depends on what you have in mind. INIT is for initiate a new file for backups and NOINIT is to backup to inside an existing backup file (if don't exists will create it).
0
 
mike2401Author Commented:
Thank you everyone.  I feel much better about the backups and appreciate everyone's input!

Here's the part I hate most about expert's exchange: allocating points without offending the kind people who took time to help others.  My apologies in advance if I don't do the allocation properly . . .
0
 
mike2401Author Commented:
Awesome info everyone!  THANK YOU!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
allocating points without offending the kind people who took time to help others
The idea is not giving points for our time but giving point to the answer or answers that helped you solving the problem, so if someone in the future has the same issue can come here and see which solution was provided to solve it.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now