Maintenance Task - Transaction Log BackUp

Question

Hi,

I have SQL server 2005 SP2.

I have set up a Maintenace Job of taking Transaction Log BackUp.

However its giving me error message as below. Please advice.

.............

Date  11/4/2009 7:30:01 AM
Log  Job History (Transaction Log.Subplan_1)

Step ID  1
Server  SDOHHQAPP03
Job Name  Transaction Log.Subplan_1
Step Name  Subplan_1
Duration  00:00:42
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: MOIL\DOH-SVC-SQL-APP03. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:30:01 AM  Progress: 2009-11-04 07:30:41.89     Source: {E60DBA40-D04E-43F9-9BA0-5ACE08245027}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  7:30:01 AM  Finished: 7:30:42 AM  Elapsed:  41.141 seconds.  The package execution failed.  The step failed.

Answer 1

Hi Rubsay,

You will need to grant the account running the SQL Agent windows service the necessary permissions. (Like backup folder access permission)Alternatively, you can run individual job steps under another security context by specifying a proxy account to run the step.

If the agent account is not the admin on the server , you could also give a try to add the user SQL agent starts up with to the "SQLServer2005DTSUser" user group  at the OS groups.

If this doesn't solve , have a look here

http://support.microsoft.com/kb/918760

http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/0eca38cf-fa4a-4d8f-99fc-f642d8652d6c

---

Thanks, Leks

Answer 2

Hello Lekss...

The service account against which the job is being executed 'MOIL\DOH-SVC-SQL-APP03', i created a login for it and assigned sysadmin role. Still it gives me same message.


....................................................................................
Date  11/4/2009 9:30:02 AM
Log  Job History (Transaction Log.Subplan_1)

Step ID  1
Server  SDOHHQAPP03
Job Name  Transaction Log.Subplan_1
Step Name  Subplan_1
Duration  00:00:35
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: MOIL\DOH-SVC-SQL-APP03. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  9:30:02 AM  Progress: 2009-11-04 09:30:35.20     Source: {E60DBA40-D04E-43F9-9BA0-5ACE08245027}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:30:02 AM  Finished: 9:30:35 AM  Elapsed:  33.281 seconds.  The package execution failed.  The step failed.

Answer 3

I think even now the backup gets generated .
Check the sp_configure values and see if "allow updates" is set to 1 by mistake and reset it to 0 and run your job and check your job history now.

---

Thanks, Leks

Answer 4

How do i check the sp_configure values??

Answer 5

Run this,

sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'allow updates',0
go
reconfigure with override
go

---

Thanks, Leks

Answer 6

Check out 1. Your database is in full recovery mode. 2. Perform the full backup on database. After these two run the tlog backup job again. HTH

Answer 7

Recovery mode is FULL.

Should i perform Bull backup manually via Rclk Dbase-Tasks-BackUp?? And should it be places in same folder as where transaction logs are being placed???

Any naming conventions to be followed ???

Answer 8

Hi Rubsay ,

Have you tried things in my last post ?

---

Thanks, Leks

Answer 9

You can take full backup manually. Also you can give the naming convention which is easily understand by you or your organisation.

HTH

Answer 10

You can take full backup manually. Also you can give the naming convention which is easily understand by you or your organisation.

HTH

Hi ,

If the database full backup was not taken before , the error would be
BACKUP LOG cannot be performed because there is no current database backup.  BACKUP LOG is terminating abnormally.

And also , if the database recovery isn't full (He couldn't have this selected this database when the maint plan was created) .

---

Thanks, Leks

Answer 11

Thanks Lekss...

sp_configure 'show advanced options',1 go
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

 

reconfigure with override go
Command(s) completed successfully.

sp_configure 'allow updates',0 go
Configuration option 'allow updates' changed from 0 to 0. Run the RECONFIGURE statement to install.

reconfigure with override go
Command(s) completed successfully.


.......................................
........................................

After doing the above, i run the Transaction Log Maintennce Job manually from SQL Server Agent - RClk - Start Job. But i am still getting the same error message...

Executed as user: MOIL\DOH-SVC-SQL-APP03. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:57:52 PM  Progress: 2009-11-04 12:58:33.02     Source: {E60DBA40-D04E-43F9-9BA0-5ACE08245027}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:57:52 PM  Finished: 12:58:33 PM  Elapsed:  41.047 seconds.  The package execution failed.  The step failed.

Answer 12

Hi Rubsay ,

Have you also checked whether this service account has read/write folder privileges at the folder where the backup is going to be taken ?

---

Thanks, Leks

Answer 13

Yes...I did it now and ran the job again.

But still the same error message.

Answer 14

Have you ticked check backup integrity in your package ?
IF yes , uncheck and run the job once.

---

Thanks, Leks

Answer 15

Ok, i Modified the Transaction Log Maintenance Job by unchecking Verify Backup Integrity.

But sadly, still the SAME old error message. This error doesnt seem to budge!

:(

Answer 16

We need error messages - whih you need to go look for in the right place. Thge egent job level doesn't say what errors SQL Server returned. For that you either should look at history for the maint plan (right-click the plan), or the report file which you can specify for a maint plan.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 17

Try taking a backup of the database log using Management Studio or using T-SQL:

BACKUP LOG <database name> TO DISK = '<path and filename>

See if the above steps throws any errors. If yes, please provide the error message. The above snippet is not enough to troubleshoot the issue. As Tibor said, we need a Maint Plan report with the exact failure message to dig into the root cause.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 18

Hello Amit.

When i try to run the below command, i get error message. FYI, i had logged on to Mgt Studio using the service account that the job is being run as (MOIL\DOH-SVC-SQL-APP03).

BACKUP LOG New_Oil_Export TO DISK = 'T:\Transaction_Logs_OilExport\New_Oil_Export.trn'

Processed 1 pages for database 'New_Oil_Export', file 'NewOilExportDev_Log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.215 seconds (0.033 MB/sec).

.............

And when i changed the option to log report, the report is generated as below.

Execute Maintenance Plan

- Execute maintenance plan. Transaction Log (Error)

Messages

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

------------------------------
ADDITIONAL INFORMATION:

Job 'Transaction Log.New_Oil_Export' failed. (SqlManagerUI)

 

 

Answer 19

We need to see the errors for the maint plan (not the egent job) or the maint report file. Just knowing that an error occurred doesn't help us to determine what the problem is. We need error messages. Without that we are running blind.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 20

Hello Tibork.

When i Rclk Transaction Log Maintenance Plan (Under Management), View History - All the history records have a green tick mark against them. And there are no details in the pane below.

But when in Rclk the Transaction Log Job under SQL Agent, i see RED cross marks against the history records....!

Where can i find detailed error message to forward it to you guys? Please advice.

I have already sent you the details of the error generated in the report...!

Answer 21

I deleted the Maintenance Job and created it again. Its working now. Strange, as i am not sure what was going wrong the first time...!

Answer 22

Just for the record, the nest step would have been to make sure you configured a maint report file and then check that file for error messages.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi