thefumbler
asked on
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?
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?
ASKER
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','
/*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.
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','
/*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.
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?
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?
ASKER
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::ReportIoErro
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'..
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::ReportIoErro
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?
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?
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
ASKER
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