Solved

Corrupted Log - Can't Attach Database

Posted on 2006-06-21
14
1,085 Views
Last Modified: 2012-06-27
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?

0
Comment
Question by:thefumbler
  • 7
  • 7
14 Comments
 
LVL 1

Author Comment

by:thefumbler
ID: 16956805
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
0
 
LVL 1

Author Comment

by:thefumbler
ID: 16956953
Tried running this script from
http://www.sqlmag.com/Files/09/26044/Listing_03.txt
================
LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

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

IF @@ROWCOUNT = 1
BEGIN
   COMMIT TRAN
   RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
   ROLLBACK
   RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- 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.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO


====================
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960819
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?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960832
Also, how do you know your log was corrupt? Did you receive a message to that effect?
0
 
LVL 1

Author Comment

by:thefumbler
ID: 16960991
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'..
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961023
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?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961035
sorry d:\
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961042
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?
0
 
LVL 1

Author Comment

by:thefumbler
ID: 16961117
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961169
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.
0
 
LVL 1

Author Comment

by:thefumbler
ID: 16962880
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.  

0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 16963131
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..

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx


Also these links talk about the errors you have received:
http://support.microsoft.com/kb/328354  << about error 9004, check this out first

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/cfc51c0173d901b0/5cc115b5d771f8%235cc115b5d771f8 << 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.


0
 
LVL 1

Author Comment

by:thefumbler
ID: 16965474
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.
0
 
LVL 1

Author Comment

by:thefumbler
ID: 16978891
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)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
and,
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now