Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Will this prevent logs from filling-up?

Posted on 2015-01-05
10
Medium Priority
?
120 Views
Last Modified: 2015-01-09
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
Comment
Question by:mike2401
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 8

Accepted Solution

by:
Dung Dinh earned 1200 total points
ID: 40532886
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
 
LVL 25

Expert Comment

by:jogos
ID: 40532982
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
 

Author Comment

by:mike2401
ID: 40533455
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mike2401
ID: 40533476
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
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 40534444
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
 
LVL 8

Expert Comment

by:Dung Dinh
ID: 40534958
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
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 400 total points
ID: 40537463
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
 

Author Comment

by:mike2401
ID: 40540332
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
 

Author Closing Comment

by:mike2401
ID: 40540351
Awesome info everyone!  THANK YOU!
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40540352
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question