Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a SQL Server 2008 R2.

I got a mail that the backup had failed, I opened the SQL log viewer and started browsing. And i found these two errors a bunch of times.

error 1:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message Error: 9001, Severity: 21, State: 1.

error 2:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message
The log for database 'DB_NAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

After a little googling, I had read a few times that the errors indicated that the DB or log file maybe corrupt. I read that I could confirm it, by running a DBCC CHECKDB 'DB_NAME', so i did, and here are the last to lines of the output:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_NAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The output shows that the DB isn't corrupt. But can the log file still be corrupt?

I also read that one solution might be run something called "Emergency Mode Repair" to fix the corrupt files. But some users did NOT recommend it.

Could another solution be to delete the log file completely and then create a new?.

share|improve this question
I think we need a little more context about how you got to this state. We see that a backup failed, but that is a symptom. What happened to the log to get to this scenario in the first place? Did someone try to do something with the files while the server wasn't running? Do you have a valid backup from before this situation occurred? And no, deleting the log file completely is not likely to work out for you... – Aaron Bertrand Feb 4 at 14:46
I can't see any other error events in the event log. I'm a DB and IT sysadmin(trainee/student) at web-coding house, so I haven't been using the DB my self, but one of our developers have. So I don't know exactly what happend to the DB. Only that the backup stopped working for that particular DB. I don't have valid backup of my DB. – Arviddk Feb 4 at 14:55
I think you need to get more information from the developers who have been meddling with this database. Not everything they would have done will show up in the error log. – Aaron Bertrand Feb 4 at 15:07
You don't have a valid backup? So you only keep the most recent backup, but you don't validate they can be restored before deleting them? Sadly this is a pretty expensive lesson that most users have to learn the hard way before they realize they need a recovery plan not a backup plan. Don't bother taking backups at all if you're not going to periodically check that they can, in fact, be restored. – Aaron Bertrand Feb 4 at 15:09

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.