Corrupted Log - Can't Attach Database

In SQL 2000, sp4 we are now getting this error when trying to attach this 10gb database.

Msg 9004, Level 21, State 10,
An error occurred while processing the log for database 'mydb'

Initially when the server was rebooted, Enterprise Manager indicated that the database was still being loaded (after) and an administrator tried to detach the database to try reattaching, which may have corrupted the log and is where we are at now.  The previous night's backup was missed, so we may have to go back 2 days if we need to restore from tape.  

We tried using the  sp_attach_single_file_db to bypass the log if it was corrupted, but the result is:
Msg 1813, Level 16, State 2, Server MyServer, Line 1
Could not open new database 'sgla'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\SQLData\mydb_log.ldf' may be incorrect.

Any suggestions other than restore from tape?

Who is Participating?
MikeWalshConnect With a Mentor Commented:
Makes perfect sense about your organization..

Some things you can do are backup to disk first then go to tape. I have had some issues with backing up to tape.

SQL Server handles its IOs pretty solidly in SQL Server 2000 and above (especially SQL Server 2000 Service Pack 4, which you should be on if you are on SQL 2K). The tape vendor would blame things on SQL because it makes it easier on them :) But SQL Server isn't going to cause a stuck tape.

How was the server brought down? Was it done cleanly?

Normally an IO problem is caused by something after SQL. SQL Hands it's IO request off to the OS and then it is written to the disk, essentially. If you have online virus scanners that could have caused an issue with corruption. Any scan on demand features should be disabled if possible, but at worst they should be set to exclude your .mdf and .ldf files. Even the scan for not on demand should exclude these files.

Any disk controllers that have caches better have batter backups on them. SQL assumes data is written once the ack is sent back from the controller. Even if it it has not been written to disk, it will still send an ack because it is in the cache. If power goes out and there is no backp on the cache, you just lost your IO and now have corruption.

I would reccomend reading this whitepaper on SQL Server IO. It helps give an understanding of the process better than I could ever explain..

Also these links talk about the errors you have received:  << about error 9004, check this out first << Error 1813

Just note that you should save a copy of your .mdf and .ldf that you have detached in case you need to work with Microsoft support. You risk losing data here because you have may have had transactions running when you received the corruption.. Looking at the timing and the fact that no one should have been in the system, you may be alright here.. But be prepared that you may need to restore earlier backup.

You may still fail with the error 1813 on the clean server, it sounds more like an issue with the attach approach and your files than the database server.

The newsgroup article talks about the 1813 error and gives a suggestion to try. In your case you may have already lost your .ldf file so this may not be the end of the world.

I would definitely watch your system carefully and investigate the cause. You don't want to be in this situation again. Read the IO whitepaper and talk to Microsoft Support.

thefumblerAuthor Commented:
I tried attaching the database from Enterprise Manager after renaming the old log file to mydb_log.old and then specifying the new log file should be
newmydb_log.ldf in the attach database window, but still got this error message.

Error 1813
Could not open new database 'mydb' CREATE DATABASE is aborted
Device activation error.  The physical file name 'd:\SQLData\mydb_log.ldf' may be incorrect
thefumblerAuthor Commented:
Tried running this script from
LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1


UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

   RAISERROR('emergency mode set', 0, 1)
   RAISERROR('unable to set emergency mode', 16, 1)


EXEC sp_configure 'allow updates', 0

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')

/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.


-- Set database options and recovery model as desired.

But the result was unable to set to emergency mode as follows:
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

(0 row(s) affected)

Server: Msg 50000, Level 16, State 1, Line 16
unable to set emergency mode
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Well don't go right to emergency mode first.. Why did the database get detached in the first place? This is not a first resort fix for things. Obvioulsy you know now that a ironclad backup strategy is good, but short of that you will need to potentially have some data loss.

Rebuilding the transaction log is a LAST RESORT.. this can cause issues with any transactions that were in various stages of progress. Rebuilding the Log means you cannot guarantee the ACID properties.

the error you are getting:

Could not open new database 'mydb' CREATE DATABASE is aborted
Device activation error.  The physical file name 'd:\SQLData\mydb_log.ldf' may be incorrect

sounds more like your attach is failing because that drive and folder does not exist, or the service account does not have proper permissions.. Can you look into these?
Also, how do you know your log was corrupt? Did you receive a message to that effect?
thefumblerAuthor Commented:
Backup strategies were recommended and not taken which I can't control.  

At this point I'm not concerned about the loss of transactions - they should be minor if any, very little was occuring....all users should have been out of the db anyway.

Folder definitely exists, selecting from Enterprise Manager browser.  However, ..service account permissions might be an issue...I may have been using the sa account to attach.

Also I found an I/O error somehow related to the tape device immediately before the issue developed but apparently after a 'successful' recovery.  Relevant SQL log below:
2006-06-21 16:48:07.54       spid5      Starting up database 'tempdb'.
2006-06-21 16:48:07.82       spid11      Recovery of database 'mydb' (8) is 0% complete (approximately 21 more seconds)
2006-06-21 16:48:08.34       spid11      Recovery of database 'mydb' (8) is 23% complete (approximately 1 more seconds)
2006-06-21 16:48:09.35       spid11      Recovery of database 'mydb' (8) is 99% complete (approximately 0 more seconds)
2006-06-21 16:48:09.35       spid11      Recovery of database 'mydb' (8) is 99% complete (approximately 0 more seconds)
2006-06-21 16:48:09.48       spid11      Recovery of database 'mydb' (8) is 100% complete (approximately 0 more seconds)
2006-06-21 16:48:09.50       spid11      1 transactions rolled back in database 'mydb' (8).
2006-06-21 16:48:09.54       spid11      Recovery is checkpointing database 'mydb' (8)
2006-06-21 16:48:09.64       spid3      SQL global counter collection task is created.
2006-06-21 16:48:09.64       spid3      Recovery complete.
2006-06-21 16:50:31.41       spid51      Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure
2006-06-21 16:53:21.35       spid51      Internal I/O request 0x432014E0: Op: ReadPipe, pBuffer: 0x00F20000, Size: 65536
2006-06-21 16:53:21.35       spid51      BackupMedium::ReportIoError: read failure on backup device '\\.\pipe\022C032C'.
2006-06-21 16:56:10.62       spid51      Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure
2006-06-21 17:00:29.34       spid51      Starting up database 'mydb'.
2006-06-21 17:00:29.36       spid51      An error occurred while processing the log for database 'mydb'..
Well you definitely need to look at your disk system to trace down the problem. Sounds like you may have had some IO issues.

What sort of disk setup do you have? Is your e:\ drive still good?
sorry d:\
also it looks like your server rebooted around 4:45pm.. Did something happen such as a power failure? Does your storage have a Write Cache? Is that cache protected by a batter backup?
thefumblerAuthor Commented:
Everything on the disk system (d:\) except for the db seems normal - no problems now, no errors to be found.  Its a Dell server with a single raid 5 array, both logs and db.  

I believe, the reboot was a result of a applying a service pack to another application, nothing too extraordinary that 'should' have affected SQL.
well your production Database server should be protected from someone applying a service pack without:

1.) Testing it in a QA that mimics (as close as possible) Prod

2.) Reviewing it with a chance control board

3.) Creating a full backup of all databases on the server

and the whole process should involve the DBA.

It is possible that this upgrade could have someone caused a burp in the IO and that was the issue. You should still do a root cause analysis.. Did the upgrade do a clean reboot, or did someone have to do a dirty shutdown?

You should be able to attach your .mdf now make sure you type your path correctly and make sure permissions are not an issue.
thefumblerAuthor Commented:
Correction - the original issue for the reboot was a stuck tape not installation of a service pack.  

Right now I am preparing another spare box to attempt to attach the database.   Interestingly the tape backup vendor thinks its an SQL problem, so a clean box might solve it.  

fyi - I do appreciate your suggestions, but please recognize this a relatively small business and I suspect you work in a much larger IT structure.  It certainly could be done better, but in reality budgets are pretty tight here.  

thefumblerAuthor Commented:
After loading the spare server and configuring with exactly the same version of SQL with SP4, and copying the mdf data files over, we got the exact same messages as before.  
Msg 9004, Level 21, State 10,
An error occurred while processing the log for database 'mydb'

So the corruption is definitely in the db itself not just the SQL install.   So nothing else helped and we were forced to restore from tape.  

The reason for the need to get the corrupted database working was that ordinarily nightly backups have worked well and db restorations have been made from the tape in the past, but this time, through a series of human errors the most recent tape version was several days old.  

Is there a specific recommended method for scripting a backups to disk on a rotating basis similar to tape - such as Mon, Tue , Wed, Thur , Fri and then overwriting the Mon file?  Disk space wil be the problem.
thefumblerAuthor Commented:
If I need to open another question on this - let me know, but ...

Thinking more on a broader basis for SQL backups and availability, what might be some options for a low-cost type of server redundancy to eliminate or reduce possible server failure to
1 - bypass or reduce exposure to the potential IO errors (as described in the article)
2 - increase geographic dispersity and therefore risk of complete data loss

Cost will be a big factor but we do have 3 other small locations connected by VPN where we could setup servers....does log shipping make sense?  But since we eventually would need to have the SQL database on our same local LAN anyway, would it be better just to script a complete backup to file every 4 hours something similar to the concept of Volume Shadow Services and move those backup files offsite.    Tape backups in general seem to be expensive and not always reliable or capable of being restored so adding a disk based method that can be transferred to other locations seems to be a better alternative.

Ideally in the event of future failure, we would like to reduce disastery recovery time to 4-8 hours, which should allow some time to detect an initial failure 1-2 hours, some initial troubleshooting 1-2 hours and then restore to a backup server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.