Log file or not?

[bigozz]

I disconnected two databases from our SQL 7 server, one will reattach without a log file, the other requires that the log file be present for reattach. What is the difference between the two?

 

[tlbroadbent]

I've used sp_detach_db and sp_attach_single_file_db numerous times in SQL 7 without encountering the situation you describe. Please explain what is happening with a little more detail. Do you recieve an error message? What process are you using to disconnect and reattach? Did you delete the LDF file before attempting to reattach? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bigozz]

This is the command I used, I used the same for the mdf that worked.

sp_attach_single_file_db "test","c:\mssql7\data\test_data.mdf"

And this was the output

Server: Msg 945, Level 14, State 2, Line 1
Database 'test' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.

Totally mystified.

 

[tlbroadbent]

Is there any additional information in the SQL Error log? Fo example, does the log indicate which file could not be activated?

Did the database have any secondary database (.ndf) files associated with this database? If so, you will need to include those in the attach.

Is the MDF file read-only by chance? That will prevent attaching it. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bigozz]

Looked at the log:

2001-11-20 16:11:10.80 spid7 File C:\MSSQL7\data\test_Log.LDF does not exist - unable to activate.

There are no .NDF files associated with the database, and the .MDF is not read-only.

I just can't see what the difference is.

Still Mystified!

 

[tlbroadbent]

How much space is available on the c: drive? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bigozz]

Just checked the space on C: (well actually it was checked for me when the exchange information store refused to start because of lack of disk space) I will try to reattach the db today and let you know.

Cheers