I only have single mdf file, the transaction log file was corrupted. When I was trying to attatch the mdf file, I received the following error message. Can someone give me a help..... it's really urgent!


TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'DEV8\sqlexpress2008'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not open new database 'Smartadmin'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\MSSQL\Smartadmin_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=1813&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Trysp_attach_single_file_db via TSQL:

sp_attach_single_file_db'SmartAdmin','C:\MSSQL\SmartAdmin.mdf'

Note that I'm guessing at your data file's name - you didn't specify what it is called or where it is located.

Does this help?

Check if the below blog post by Paul Randal helps if the above solution by Aaron doesn't work:
http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

- Deepak

Anyway I've solved the problem by myself. I just want to comment thatthis solutions is not working for me, but Deepak's article is working for me. I've found this article before you guys helped me. But thanks for that.

I killed a dev db by accident (moved it between folders while it had an open transaction) and out of curiosity attempted to fix it as though it were a super-urgent client db. The above link fixed it, but I'll cut to the chase since it's a lot simpler than most of the article:

1) Set aside the database mdf and ldf you're trying to recover.

2) Create a database in the location you wanted, of the same name as the problem DB.

3) Stop the SQL Server service.

4) Copy the mdf and ldf files in, overwriting the dummy db you created.

5) Start the SQL Server service.

Done. Fixed.

Surely you're doing something SQL Server does not like, but it is unfortunate you have to shut down the entire service to perform this trick. All of the other methods in that linked article and sp_attach_single_file_db failed, but this worked like a charm. In my dev database I was left with a db that was good as new - no missing data no problems. It seems odd this works so well and yet all these other approaches get errors about an open transaction. To be fair though, my dev database is essentially a single-user file that isn't heavily used, I assume a production DB on a busy server would be more problematic.

this worked perfectly for me!!, was easy and fast.

thank you so much

Thx a lot. You saved my day.