T-log is failing

Question

T-log is failing

s*1918

Ten Centuries

Points: 1298
More actions
February 21, 2012 at 12:18 pm

#391609

One of my database Transaction log is failing, got following error:
Type: Transaction Log
Append existing
Task start: 2012-02-20T20:00:41.
Task end: 2012-02-20T20:01:22.
Failed-1073548784) Executing the query "BACKUP LOG [SharePoint_Config] TO DISK = N'D:\\Pro..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [SharePoint_Config] TO DISK = N''D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SharePoint_Config_backup_2012_02_20_200041_3106639.trn'' WITH NOFORMAT, NOINIT, NAME = N''SharePoint_Config_backup_2012_02_20_200041_2989447'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO.
also I ran the following statement to see the what is the recovery mode set..
select name, log_reuse_wait_desc, recovery_model_desc from sys.databases where name = 'SharePoint_Config';
and looks like recovery_model_desc =FULL.
I am running on Sql server 2008 R2.
Could anyone assist me on this.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do you have log backups that are succeeding for different databases to the same directory?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Corruption in the log is reasonably easy to fix, as long as it's the inactive portion. You've got backups right up to when this started?

Switch to simple recovery, run a checkpoint, switch back to full, take a full or differential backup, try the log backups again. If anything in that series fails don't go any further.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s*1918 Ten Centuries Points: 1298 More actions · Answer 3

s*1918

Ten Centuries

Points: 1298

February 22, 2012 at 8:11 am

#1450325

Yes very much!

s*1918 Ten Centuries Points: 1298 More actions · Answer 4

Yes, I do have a backup!

I will do as per your advice!

Thanks!

s*1918 Ten Centuries Points: 1298 More actions · Answer 5

Can I change the recovery to simple and run a checkpoint when users are accessing the DB?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes, but this is something better done while the DB is idle.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s*1918 Ten Centuries Points: 1298 More actions · Answer 7

s*1918

Ten Centuries

Points: 1298

February 22, 2012 at 8:46 am

#1450370

Better I will do after office hours.

Thanks.

s*1918 Ten Centuries Points: 1298 More actions · Answer 8

Now Transaction log backup is working fine, after I did the above said steps. Now my question is in future if I faced the same situation, I need to do the same steps.

Also why in sys.databases table the column log_reuse_wait_desc shows "LOG_BACKUP'. Also I would like to know how to avoid these log errors going forward.

Appreciate your advice.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Kargan (2/23/2012)
Also why in sys.databases table the column log_reuse_wait_desc shows "LOG_BACKUP'. Also I would like to know how to avoid these log errors going forward.

That's not an error. That's saying that the log is not being reused because portions are held active pending a transaction log backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s*1918 Ten Centuries Points: 1298 More actions · Answer 10

s*1918

Ten Centuries

Points: 1298

February 23, 2012 at 7:49 am

#1450873

Got it! Thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 11

ybz199009 (2/24/2012)
blabla

spam. reported.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

s*1918 Ten Centuries Points: 1298 More actions · Answer 12

Hi Gail,

I got the same "LOG_BACKUP" in sys.databases. I will do the same steps(full to simple and then run checkpoint, change to full, then take full or differential backup, then trnlog backup).

Is there any way I can avoid this in future?

Appreciate your help!

Thanks