how to repair my SQL log file ?

Hi Experts,

on my sql server I cannot backup the logs anymore.
The full backup works but not the log backup.
I have a lot of event ID 3041.
What can I do to repair my logfile ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Can you check the sql errorlog and paste the error message you see before this ID 3041;
Eprs_AdminSystem ArchitectAuthor Commented:
this server is in german, not my fault :-(
Eprs_AdminSystem ArchitectAuthor Commented:
what else can I do ?

now I make dbcc checkdb
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Aneesh RetnakaranDatabase AdministratorCommented:
without the error its really hard to do anything ? you can still paste the error here
Eprs_AdminSystem ArchitectAuthor Commented:
when I want to backup the logs with _> backup log...

Meldung 3049, Ebene 16, Status 1, Zeile 1
BACKUP hat eine Beschädigung im Datenbankprotokoll erkannt. Weitere Informationen finden Sie im Fehlerprotokoll.
Meldung 3013, Ebene 16, Status 1, Zeile 1
BACKUP LOG wird fehlerbedingt beendet.
Eprs_AdminSystem ArchitectAuthor Commented:
if you want I have the full DBCC CHECKDB out put
no errors in the output
Eprs_AdminSystem ArchitectAuthor Commented:
I read someting about to change to simple recovery mode.
Do I loos data with it ?
Aneesh RetnakaranDatabase AdministratorCommented:
seems like that's your best option, change the recovery back to simple and then back to full. Do this after you took the full backup.
Alternatively you can restore the full backup on a test server, issue a log backup statement to see if its working. If not change the recovery back to simple and then back to Full and see whether the backup log is working.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eprs_AdminSystem ArchitectAuthor Commented:
do I loose data when I switch back to simple ?
Aneesh RetnakaranDatabase AdministratorCommented:
no; you wont; before you try it on production, restore the backup and test
Eprs_AdminSystem ArchitectAuthor Commented:
there was no time to restore to a test server.
The backup was too old.

Please can you explain in some words, why we not loose data when switching to SIMPLE mode ?
My doubts are, the LOG file was full with 140GB.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please can you explain in some words, why we not loose data when switching to SIMPLE mode ?
Only non committed data would be lost but if you can assure that no one is connected that won't be a problem.
Like Aneesh said, the best option is to change the recovery model of that database to simple so it will break the backup chain. Then you changed it again to full recovery model so you can start a new backup chain. It's highly recommended that you perform a full backup immediately after set to full recovery model.
Eprs_AdminSystem ArchitectAuthor Commented:
last night I did this.
I change the model to SIMPLE.
Then the LOG file was still 140GB but I was able to shrink it.
Then I changed model back to FULL.
And of course we made also a full backup. Now I have seen the LOG backups also working.
Eprs_AdminSystem ArchitectAuthor Commented:
Last night no one was connected.
Data in a transaction log file is always committed ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's committed if auto commit is on (the default for SQL Server) or explicitly COMMIT statement exists.
Eprs_AdminSystem ArchitectAuthor Commented:
where can I find this default setting ?
I just wanna check it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Scott PletcherSenior DBACommented:
The error message means:

BACKUP (recognized) corruption in the database log.  You'll find further information in the error log.  [BACKUP hat eine Beschädigung im Datenbankprotokoll erkannt. Weitere Informationen finden Sie im Fehlerprotokoll.]

Therefore, if you want more info on what happened, you need to check the SQL error logs for the first time the error occurred with this log, because SQL often will only output full details for an error the first time it occurs.
Scott PletcherSenior DBACommented:
Data in a transaction log file is always committed ?

No.  By definition, the log will contain uncommitted data all the time, except in the very rare case when a db has had absolutely no deletes, inserts or updates since the last time the log was backed up.  [Almost no dbs are that static, unless perhaps they are set to "read only".]  

The above is true regardless of whether autocommit is on or off.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.