Roshan_wp


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.





Re: Error when issuing BACKUP LOG mydb with truncate_only

Jarret


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






Re: Error when issuing BACKUP LOG mydb with truncate_only

Roshan_wp

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.







Re: Error when issuing BACKUP LOG mydb with truncate_only

Jarret

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

backup log DB_Name with truncate_only

Jarret





Re: Error when issuing BACKUP LOG mydb with truncate_only

Roshan_wp

No. its not allowing me to run the backup log statement directly.

Roshan.






Re: Error when issuing BACKUP LOG mydb 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





Re: Error when issuing BACKUP LOG mydb with truncate_only

Roshan_wp

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @DBName as Varchar(120)

select @DBName = DB_name()
dump transaction @DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.






Re: Error when issuing BACKUP LOG mydb with truncate_only

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





Re: Error when issuing BACKUP LOG mydb with truncate_only

Hugo From Spain

You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.