Transaction Log Through Maintenance Plan

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction Log Through Maintenance Plan

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-10 : 23:24:22

Hi Experts,

After issuing the statement,

BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY

The transaction which are going till then have stopped.

Transaction will generate through the Maintenance plan and I have recreated the Maintenance plan for securing the Transaction Log generation, but is not working.

Normally transactions log will generate for every 15 min Duration.

Can Any one help in this regard.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-10 : 23:33:28

In addition to the above

the error generated as

Message
Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:30:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:03 PM Elapsed: 2.75 seconds. The package execution failed. The step failed.

Please advise me this happened ever since I have issued on T-SQL as
BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 08:38:41

Your question is not clear.What actually happened? You have broken the log chain with that statement.You won't be able to restore transaction log backup taken after that point.

heavymind
Posting Yak Master

115 Posts

Posted - 2009-02-11 : 08:48:31

What do you mean under generating transactions through maintenance plan?
running backup log with truncate_only you broke transaction log chain but it should not have broken maintenance plans execution...

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-11 : 14:35:31

In order for the transaction log backups to work again, you'll need to kick off a full backup. Do not truncate the transaction log! If you do, always follow that command with a full backup to start the log chain again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-11 : 15:24:39

This command executed BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY
on the transaction log File.
Then onwards no transaction log for 15 minutes were generating.

I have several times created the Maintenance plans but of no use.

The error Message:

Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:30:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:03 PM Elapsed: 2.75 seconds. The package execution failed. The step failed.

Please advise as it is now imminent to enable it.

Many thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-11 : 18:12:40

Did you read my last post? You must perform a full backup in order to start the transaction log chain.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-11 : 18:27:46

Thanks TkIzer

I have done exactly on taking the whole Server databases backed up and re started the Maintenance plan for Transaction logs which will do for every 15 minutest duration.

Error:
Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:15:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:15:00 PM Finished: 10:17:75 PM Elapsed: 2.75 seconds. The package execution failed. The step failed.

I have executed using under Master

sp_configure 'allow updates', 0

reconfigure

but still giving this problem.
Im getting nervous as all my options have gone.

please Advise me.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-11 : 22:45:32

Can anyone help me in this regard.

Its urgent for me.

As TKizer Pointed out: doing the BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY then took Full Backup then started the Maintenance Plan for transaction Log.

As I have done the Full Backup of the entire Server databases and restarted the Maintenance Plan for transaction log entries to go for every 15 minutes duration.

I have done also did

sp_configure 'allow updates', '0'
RECONFIGURE

Same error

Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:15:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:15:00 PM Finished: 10:17:75 PM Elapsed: 2.75 seconds. The package execution failed. The step failed

Please someone throw their Expertise as where I am lacking to do.

many Thanks for all of you took part in this problem

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 22:54:28

quote:
Originally posted by pdset

Can anyone help me in this regard.

Its urgent for me.

As TKizer Pointed out: doing the BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY then took Full Backup then started the Maintenance Plan for transaction Log.

As I have done the Full Backup of the entire Server databases and restarted the Maintenance Plan for transaction log entries to go for every 15 minutes duration.

Can you start all over again?

I have done also did

sp_configure 'allow updates', '0'
RECONFIGURE

Why are you doing this? You don't need this
.

Same error

Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:15:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:15:00 PM Finished: 10:17:75 PM Elapsed: 2.75 seconds. The package execution failed. The step failed

Please someone throw their Expertise as where I am lacking to do.

many Thanks for all of you took part in this problem

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-11 : 23:00:48

Sodeep Thanks

Can you please advise me what is to be done now.

Still the same

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-11 : 23:55:33

Should I execute below command on T-SQL under Master Database only or all other databases on the server?

BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY;

then took Full Backup of All databases on the server;

then start the Maintenance Plan for transaction Log as follows:

Remove the Old Maintenance Plan (Transaction Log 15 Minute Duration);

Create afresh New Maintenance Plan for Transaction log and execute it from the SQL Server Agent folder.

(I Have Just done as above but no relief)

Please Advise me.

Many Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-12 : 00:37:23

Do not truncate the transaction log! That's what got you into this mess.

Run a full backup, then manually run BACKUP LOG command and post the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-12 : 01:17:30

Thanks Tkizer for knowing wher I went wrong exactly.

I really appreciate your patience replies for my botheration.

This what I have done:

I did Run a full backup; (For All databases, including System Level)

then manually run BACKUP LOG command as BACKUP LOG CDPCENTRAL WITH TRUNCATE_ONLY under Master Database in the T-SQL;

the error:

Executed as user: Server01\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:40:00 PM DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:40:00 PM Finished: 5:42:75 PM Elapsed: 2.75 seconds. The package execution failed. The step failed

Please Guide me in the following:

(May you suggest I should run backup log Command for each and every database in the instance after taking all backups?)

Should I recreate (on delete the existing maintenance plans)the Maintenance Plans again at this stage?

many thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-12 : 02:01:37

You aren't following the directions correctly. Do not run TRUNCATE_ONLY. Why are you running this on the master database? That makes no sense. Do not truncate any transaction logs. Backup the transaction log to a file for whiever database you are interested in. Do this manually.

The point of running a manual transaction log backup to a file is to see what is going on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-12 : 17:37:41

Hello TKizer

This is the sample output when after taking full backup of all the databases then on executing the 'backup log' for each database and the sample is given for you.

Processed 7 pages for database 'BAIRNSDALE', file 'BAIRNSDALE_log' on file 1.
BACKUP LOG successfully processed 7 pages in 0.173 seconds (0.295 MB/sec).

Should I recreate the Maintenance plans after backing up log and pull the log transactions.

Thanks for your advise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-12 : 17:50:58

The output is correct, so the transaction log backups are working. So you've got something wrong in the maintenance plan. I don't ever use maintenance plans, so I can't help you specifically with that. I use my own custom scripts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-12 : 18:38:47

Thanks Tkizer.

I have recreated the Maintenance plans but saying some thing not coming with this particular error relates to:

DTExec: The package execution returned DTSER_FAILURE (1).

Some one has suggested me with doing when Maintenance plans giving above error, then

sp_configure 'allow updates',0
RECONFIGURE

Still the same.

Message
Executed as user: DHSV0\SQLProdAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:32:03 AM DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:32:03 AM Finished: 10:32:06 AM Elapsed: 2.453 seconds. The package execution failed. The step failed.

Do I Need to correct anything within the maintenance plans setup?

Can some one still help me.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 18:44:50

Why don't you create fresh new Maintenance plan and try with database with you have just done Tran Log backup. What service packs do you have in your server?

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-12 : 19:15:45

Thanks Sodeep.

I do have SP2 2005.90.3042

I have created new Maintenance plan for capturing the Transaction Logs and interval mentioned as 15 minutes duration.

but Still the same Error

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-02-12 : 19:26:32

Sodeep,I have setup with the log report:

ERROR:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:19:28 AM
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:19:28 AM
Finished: 11:19:31 AM
Elapsed: 2.484 seconds

This is the output

Please advise me

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources