Transaction Log Gorowing and wont backup

[arrowhouse]

Dear all,

I have a database, its recovery model is full. For reasons I wont bore you with, the transaction log back up was not set up, but a full back up has been running daily.

The transaction log is now very large. I have tried to do a back up of the transaction log, to allow it to truncate, but it errors after many many hours & I get the following in the SQL Logs:

BACKUP failed to complete the command BACKUP LOG [MyDatabase] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDatabase_Tran_log_20040604' WITH NOINIT , NOUNLOAD , NAME = N'MyDatabase backup', NOSKIP , STATS = 10, DESCRIPTION = N'To clear full log', NOFORMAT

Any ideas as to what is going wrong?

A full back up works fine, but does not truncate the log. My current plan is to:

Do a full back up,
change the recovery model to simple.
issue a checkpoint
set the recovery model back to full
ensure full and transaction log back up maintainance tasks are being done.

Can you see any problems with this?

Thanks for your time

 

[JayKusch]

Looks to be a sound plan.

Thanks

J. Kusch

 

[arrowhouse]

FYI in the end I did a full back up
Then
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
This allows the truncation of the log to occur, but does not create a back up of the log.
Then
Full back up
& shrunk the log file after that & all seems ok now.

 

[SQLSister]

Arrowhouse, I'm sure you don't need this reminder after what you put in the first post, but to others who are reading this at a later date, don't forget to set up the transaction log backups at this point or the problem will reoccur.