Solved

Will this prevent logs from filling-up?

Posted on 2015-01-05
10
107 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 7

Accepted Solution

by:
Dung Dinh earned 300 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
 

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 68

Assisted Solution

by:Qlemo
Qlemo earned 100 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now