Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @DBName as Varchar(120)

select @DBName = DB_name()

dump transaction @DBName with truncate_only

GO

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

¡°BACKUP failed to complete the command exec spm_tranlog¡±

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are: Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as

declare @DBName as varchar(120), @sql as varchar(200)

select @DBName = DB_name()

select @sql = 'backup log ' + @DBName + ' with truncate_only'

execute (@sql)
go

Hope this helps.

Jarret

Can you run the backup log statement directly without getting an error

backup log DB_Name with truncate_only

Jarret

Can you post the error message you are getting and the exact statement you are running

Are you sure you have permissions to do this You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window

Bypassing the stored procedure, can you try to run this:

declare @DBName as Varchar(120)
select @DBName = DB_name()
dump transaction @DBName with truncate_only

And also...

declare @DBName as Varchar(120)
select @DBName = DB_name()
backup log @DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret