Ooops! Cannot reattach database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ooops! Cannot reattach database

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-29 : 05:27:23

Morning guys and gals

Me and my colleague have a little problem. I know some of you are aware that I have been having problems with hard drive space due to earlier posts and have been great to offer some solutions (Kristen, for example). However, our director kindly heard our cries for help and has installed an additional disk for our usage. Here is the problem:

We deleted a transaction log file (in EM), as it was no longer needed and took up too much space. We then unattached the database and transferred the other log files to the new disk. When attempting to reattach the database, it will not allow us to do so as it still recognises the log we deleted. This log no longer exists, and we cannot seem to rename another log file to take its place. So we are in a pickle as we cannot attach the database.

Any suggestions? I would perform some research, but this is something we are trying to resolve as quickly as possible without anyone the wiser???

Thanks in advance (I hope)!!!!!!!1

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-29 : 05:31:52

Oh, just been told that the reason why we cannot reattach is because that the server cannot locate the log file, rather than still reading it as there. Don't know if that makes a difference to any of your answers. But as it is not there, then what can we do?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-29 : 06:17:28

Just found a post exactly with the same problem as ours on this wonderful forum. So we are going to try that solution. Fingers crossed!

here is the solution if anyone is interested. Laters:

Reply author: Kolyana
Replied on: 09/13/2004 16:27:41
Message:

Solution:
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

---

Incidentally, I realize that "detatching" a database should be utilized before one should ever hope to be able to reliably follow with "attach db", but these were the cirumstances that I was given: Two files, mdf and ldf, and the ldf is corrupt so all attach methods were failing.

The above REBUILD_LOG works WONDERFULLY, although there are obviously data integrity caveats. It has, however, successfully taken the database out of 'suspect' and replaced the .ldf file nicely in this instance.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources