I detached the a database cause i needed to move the transaction log file to a different drive cause i was running out of space. It detached sucessfully but when i go to reattach the database i get an error stating: Server: Msg 9003, Level 20, State 4, Line 1 The LSN (19377:35:1) passed the Log scan in database 'baandb' in invalid

Connection Broken .

i have done this on another test server and it worked fine so i dont know why it didnt work here. Pleasse help

thanks

I did the sp_attach_single_file_db and it attached successfully and it created a new log file. Is it bad that it created a new log file, i know nothing was using the database at the time i detached it so i dont think i lost any transactions, Is there a way to test to make sure everything is ok.

Hi Daniel,

I hope you made a backup of the database before detaching it.
A quick suggestion would be to rename the *.ldf transaction log file (so SQL Server can't see it) and attach only the *.MDF file using sp_attach_single_file_db . If it all runs well SQL should create a log in the same directory of the *.MDF file. After that you can try to detach again and attach.
The bad news is if you have that error it means the MDF and the LDF where not syncronized which in turn means either the detach didn't complete sucessfully or that activity was going on in the database :(. You should put the database in single-user mode before doing this kind of operations.
If sp_attach_single_file_db doesn't work I would call Microsoft PSS to see if they can help you or recover from the backup.

You can also follow this link (http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=Xns9209BA823E332Yazorman%40127.0.0.1) but this is very risky for a production database. I used something like this (except the if else segment) when I add a problem with a damaged second transaction log but this was on a test server (no backup at the time) and it worked. In my case I couldn't attach the database because I had more then one log, and one of them was damaged (When more then one log exists SQL would not recreate a new single log).

Best of luck,

Hi again,

When I was writing the reply you posted all was fine. That's good to hear. :)
I guess that if you successfully attached the database you didn't loose any data. Anyhow, and just to make sure you can run a DBCC CHECKDB to check the database. Just notice that depending on the capacity of the server and the size of the database that can take sometime to complete.

:cool:

For future notice what would be the correct steps to do when i need to detach a database. Every thing seems fine with my database i can get into baan and see data so i assume everything went well.