asked on

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

ASKER CERTIFIED SOLUTION

Dung Dinh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

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.

mike2401

ASKER

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

mike2401

ASKER

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

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dung Dinh

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,

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mike2401

ASKER

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

mike2401

ASKER

Awesome info everyone! THANK YOU!

Vitor Montalvão

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.