SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Attach a database without LDF
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/31/2007 :  12:29:00  Show Profile  Reply with Quote
I want to attach a database that one of the developers had detach it. The LDF file was deleted after detach.

I tried to attach and rebuild the log file in SQL2005 using the following:

USE [master]
GO
CREATE DATABASE [Test] ON 
	(FILENAME = N'G:\MSSQL\Data\Test.mdf')
	FOR ATTACH_REBUILD_LOG
GO


I get the following error:

File activation failure. The physical file name "D:\MSSQLDATA\Test_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test'. CREATE DATABASE is aborted.

What should I do?

Thanks,

Canada DBA

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/31/2007 :  13:13:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
I've always used sp_attach_single_file_db.

Tara Kizer
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/31/2007 :  14:34:12  Show Profile  Reply with Quote
Thanks for the reply. I used the following code and got the same result.

USE [master]
GO

sp_attach_single_file_db N'Test', N'G:\MSSQL\Data\Test.mdf'
GO

File activation failure. The physical file name "D:\MSSQLDATA\Test_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test'. CREATE DATABASE is aborted.




Canada DBA

Edited by - CanadaDBA on 01/31/2007 14:35:08
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/31/2007 :  14:38:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
I guess your MDF file is corrupt then.

Tara Kizer
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/31/2007 :  14:40:58  Show Profile  Reply with Quote
... and there is no way to fix it?

quote:
Originally posted by tkizer

I guess your MDF file is corrupt then.

Tara Kizer



Canada DBA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/31/2007 :  14:42:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Perhaps Microsoft can fix it.

Tara Kizer
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/31/2007 :  16:45:01  Show Profile  Reply with Quote
quote:
Originally posted by CanadaDBA

I want to attach a database that one of the developers had detach it.



hmmmmmmmm

I think besides that problem, you have others



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 01/31/2007 :  17:30:28  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
I was able to use the sp_attach_single_file without any problems.
So it seems what Tara says, about mdf being bad itself may be the problem.
Go to Top of Page

MohammedU
Posting Yak Master

USA
145 Posts

Posted - 02/01/2007 :  01:57:52  Show Profile  Reply with Quote
Last would be undocumented command DBCC REBUILD_LOG but I am not sure it will work in 2005...

1. Rename existing .mdf file to .mdf_old
2. Create a new database with same .mdf and .ldf file as old one.
3. Stop the sql server
4. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
5. Rename .mdf_old to .mdf
6. Start sql server
7. You should see db in suspect mode.
and then follow the following instructions...
Note: you can't update system tables in 2005 so you can use alter database command to change to db emergency mode...

8. Change the database context to Master and allow updates to system
tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

9. Set the database in Emergency (bypass recovery) mode:

-- note the value of the status column for later use
select * from sysdatabases where name = '<db_name>'
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

If you run DBCC REBUILD_LOG without setting the database in Emergency
mode, the command does not work. You do not receive an error, but the
log is not rebuilt either.

10. Stop and restart SQL server.

If you run DBCC REBUILD_LOG without recycling the server, the following
message displays:

Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
bypass recovery mode to rebuild the log. DBCC execution completed. If
DBCC printed error messages, contact your system administrator.

11. The syntax for DBCC REBUILD_LOG is as follows:

DBCC REBUILD_LOG('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is the
physical path to the new log file, not a logical file name. If you do
not specify the full path, the new log is created in the Windows NT
system root directory (by default, this is the Winnt\System32
directory).

12. Rebuild the log with this code:

DBCC TRACEON (3604)
DBCC REBUILD_LOG('<db_name>','<log_filename>')
Go

If the command is successful, the following message appears:

Warning: The log for database '<db_name>' has been rebuilt.
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.

After the log is successfully rebuilt, the database is placed in DBO Use
Only mode. That is, the status of the database is 2048 irrespective of
what the status was previously. You must reset the status using
sp_dboption or through the SEM.

13. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name =
'<db_name>'
-- verify one row is updated before committing
commit tran
Go

14. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go

WARNING: After verifying the consistency of the database by running DBCC
CHECKDB, and fixing any errors, please make sure to check the database
for logical consistency as well. Because a new log has been built, the
transactions in the
old log are lost, hence you must also verify the logical consistency of
the data as well.


MohammedU
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 02/01/2007 :  08:57:58  Show Profile  Reply with Quote
Thanks for the complete explanatory post Mohammed!

I had to use the following in step 9.
-- note status is changed to: 98568
Alter database <db_name> SET Emergency

I did everything upto step 12: DBCC REBUILD_LOG('<db_name>','<log_filename>')
After running the DBCC command:
Msg 2526, Level 16, State 3, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.


Note: I am working on my desktop not the server and SQL Server Management Studio Express is installed on my mchine.

In the following link it says: The following dbcc commands are now dead and buried from SQL Server 2005 onwards.And REBUILD_LOG is one of them.

from http://www.sqlservercentral.com/columnists/jreade/sqlserver2005dbcccommandquickreference.asp


Canada DBA
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 02/01/2007 :  09:02:56  Show Profile  Reply with Quote
I also tried:

CREATE DATABASE MyTest ON PRIMARY 
(FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyTest.mdf')
LOG ON (FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyTest_log.ldf')
FOR ATTACH_REBUILD_LOG;


Msg 1801, Level 16, State 3, Line 1
Database 'R06_USAA_RERUN' already exists.



Canada DBA
Go to Top of Page

MohammedU
Posting Yak Master

USA
145 Posts

Posted - 02/01/2007 :  14:11:13  Show Profile  Reply with Quote
That is the reason I said "Last would be undocumented command DBCC REBUILD_LOG but I am not sure it will work in 2005..."



MohammedU
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 02/02/2007 :  13:48:59  Show Profile  Reply with Quote

quote:
Originally posted by MohammedU

That is the reason I said "Last would be undocumented command DBCC REBUILD_LOG but I am not sure it will work in 2005..."



MohammedU



Canada DBA
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/02/2007 :  15:52:37  Show Profile  Reply with Quote
Why not just restore the database from the last full backup?




CODO ERGO SUM
Go to Top of Page

Varox
Starting Member

Austria
1 Posts

Posted - 03/14/2007 :  16:02:33  Show Profile  Reply with Quote
Thank You MohammedU, you made my day (or evening, it is)! :-)

Our db server's log disk was out of disk space and I could neither backup (28 GB log) nor detach the database. Then I *ahem* somehow managed to kill the log during detaching.

Not the most important database, but still, reinstalling the application it belonged to would have been quite a hassle. I followed your instructions step by step (SQL Server 2000) and voilà: back online again!!

Benjamin
Go to Top of Page

jorgejordao
Starting Member

1 Posts

Posted - 11/28/2007 :  17:13:34  Show Profile  Reply with Quote
Hi

I've the same problem, it all goes well to the step nº9, when i try the next step

DBCC TRACEON (3604)
DBCC REBUILD_LOG('GesPOSData','C:\Programas\Microsoft SQL Server\MSDMSSQL$MSDE\Data\GesPOSData_log.ldf')
Go

i get the following message
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5180, Level 22, State 1, Line 2
Could not open FCB for invalid file ID 0 in database 'GesPOSData'.

Thanks
Go to Top of Page

LeenaAmbulkar
Starting Member

India
1 Posts

Posted - 08/15/2008 :  00:41:05  Show Profile  Reply with Quote
Thank You MohammedU,

The detailed procedure given by you helped us recover vital database for a hospital.

How do we avoid Log file size increse to a huge size , which basically started the issue.

Thanks again,
Leena
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 08/15/2008 :  01:33:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
Backup your transaction log on a regular basis. If the data is vital, you should be using FULL recovery model with regular transaction log backups such as every 15 minutes.

Shouldn't they have an experienced DBA supporting a vital hospital database?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Borik
Starting Member

2 Posts

Posted - 11/10/2008 :  13:20:51  Show Profile  Reply with Quote
Ok, that what i did to get it up and running, Steps are From getting Db out of [Suspect] mode due to corrupt Log file, can be applied to
DB attaching without Log File...

1. Set DB in Emergency Mode. ALTER DATABASE [dbName] SET EMERGENCY
2. Script Create DB, this should simplify the process if you have multiple files, groups, etc...
3. In a Generated Script Remove Log Entry
4. Add "For Attach_Rebuild_Log"
5. Rename Log files to .OLD, check in few locations like Defulat locations, Data file locations...
6. Detach DB, ignore the error, it should be gone from Dabases list, if you refresh... EXEC master.dbo.sp_detach_db @dbname = N'dbName'
7. Run Script

p.s. Posting here as it comes up on Google Search, i tough it might be helpful
Go to Top of Page

phuongkar
Starting Member

1 Posts

Posted - 07/03/2009 :  05:46:04  Show Profile  Reply with Quote
I found this way on internet but this article has been wrote by language Vietnamese, you can see http://hoclaptrinhweb.com/?page=learn_detail&id=99&CategoryId=65
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/03/2009 :  10:37:16  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
For anyone who runs across this thread from a search engine:
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

--
Gail Shaw
SQL Server MVP
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000