Solved

Will this prevent logs from filling-up?

Posted on 2015-01-05
10
110 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 69

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
 
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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 32
SQL Server Designer 19 45
Return 0 on SQL count 24 30
return table in table valued function  using dynamic sql, SQlServer 2008r2 5 21
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

832 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