Cannot Attach Database Error 1105

Question

Cannot Attach Database Error 1105

Zara Saxon

Old Hand

Points: 313
More actions
July 6, 2005 at 5:46 am

#109801

Hi I am in a bit of a catch 22 situation here. I have been given an mdf file to attach to sql but I cannot attach the database because the ldf log file is full.
Does anyone know how I can attach or restore the database from just the mdf file or if I can truncate the ldf file without having to attach it to SQL?
The actual error message is:
Error 1105: Could not allocate space for object 'PassiveMonitorActivityLog' in Database 'Whatsup' because the 'DEFAULT' filegroup is full.
Error while undoing logged operation in database 'Whatsup'. Error at log record ID (870:7736:16).
Could not open new database 'Whatsup'. CREATE DATABASE is aborted.
2 transactions rolled forward in database 'Whatsup' (5).
--------------------------------------------------------
Any suggestions would be super.
Ta muchly,
Zara

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

You can move the LDF to a different location then the DB thinks which will force a new log to be created. It will complain that a new log will be created but will do it anyway.

Another problem may be actual free space on the hard drive as well....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Zara Saxon Old Hand Points: 313 More actions · Answer 2

Hi,

I tried removing the ldf but then I get a Device activation error.

Thanks Zara

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

I agree with AJ Ahrens - it should recreate the log file if none exists.

Can you post the full device 'activation error' message?

Regards

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Zara Saxon Old Hand Points: 313 More actions · Answer 4

Hi,

SQL Prompts to create a Log File but when you click on ok it produces the following error message:

Error 1813: Could not open the new database 'WhatsUp' CREATE DATABASE is aborted.

Device Activation Error. The Physical File name 'd:\Whatsupgold\mssql$WHATSUP\Data\Whatsup.ldf' maybe incorrect.

I have tried copying the mdf to another pc but it still comes up with this error. I think the path is where the original database was created.

Many Thanks,

Zara

Phil Parkin SSC Guru Points: 246970 More actions · Answer 5

Can you overwrite the path with one that is valid before the error message appears? Sorry, I should know, but I haven't done this for a while.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Zara Saxon Old Hand Points: 313 More actions · Answer 6

We have tried attaching the database through Enterprise Manager and using Query Analyser sp_attach_single_file_db 'WhatsUp','c:\temp\Whatsup.mdf'

This gives the same error message

Could not open new database 'WhatsUp'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'D:\whatsupgold\MSSQL$WHATSUP\Data\WhatsUp.ldf' may be incorrect.

Unfortunately you cannot specify your Log file and asking SQL to create a new one as suggested has not alleviated the issue.

Marc-204254 SSC Enthusiast Points: 170 More actions · Answer 7

Try to figure read might help

http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp

Marc-204254 SSC Enthusiast Points: 170 More actions · Answer 8

Found this procedure on the try and let us know if it helped :

--1. Create Database

USE master

GO

CREATE DATABASE mydatabase

ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)

LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),

( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)

GO

--2. Add data

use mydatabase

go

create table x123(id int)

insert into x123 select 111223

--3. Detach Database

use master

go

sp_detach_db mydatabase

--4. Accidental Deletetion.

Delete mydatabase1.ldf and delete mydatabase2.ldf

--5. Try to attach mydatabase.mdf

sp_attach_db 'mydatabase','c:\mydatabase.mdf'

Error message:

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'mydatabase'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'c:\mydatabase1.ldf' may be incorrect.

Device activation error. The physical file name 'c:\mydatabase2.ldf' may be incorrect.

--6. Rename c:\mydatabase.mdf to MydatabaseXXXXXX.mdf

--7. Create database mydatabase

USE master

GO

CREATE DATABASE mydatabase

ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)

LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),

( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)

GO

--8 Stop SQL Server

--9 Delete mydatabase.mdf

--10 rename MydatabaseXXXXXX.mdf to mydatabase.mdf

--11 Start SQL Server service

--12 run the following

use Master

go

sp_configure "allow", 1

go

reconfigure with override

go

--13

update sysdatabases set status = 32768 where name = 'Mydatabase'

go

checkpoint

go

shutdown with nowait

go

--14. delete mydatabase1.ldf and mydatabase2.ldf

--15. run this query

dbcc traceon(3604)

--16. rebuild Log

dbcc rebuild_log('Mydatabase','c:\Mydatabase1.ldf')

--17.

update sysdatabases set status = 0 where name = 'mydatabase'

--18. restart sql server

--19. run the following query

use mydatabase

go

dbcc checkdb

go

dbcc checkalloc

go

backup database mydatabase to disk = 'c:\mydatabase.bak'

go

select * from x123

go