?
Solved

Attach database without log file in SQL 2005

Posted on 2007-10-05
8
Medium Priority
?
10,832 Views
Last Modified: 2009-07-24
Hi there,
We are running MOM and having problems with the database. The log file reached 86GB and filled up the whole of the D Drive it was living on.
I wanted to take a backup of the database but it kept failing, i eventually found out that this was because the external HD i was trying to backup too was FAT 32 and kept crashing at about 4GB (no alarm bells went off in my head....)
However I was able to detach and attach the database until i tried something.
I added an extra log file to the database and tried the shrink log file within Management Studio to empty the original log file and hope it would fill up the log new log file. However this didn't do anything and i detached the database again and tried copying the files onto the external drive. Again as it was fat 32 could not take the large files so formatted as NTFS and deleted the extra log file i created. I have a copy of the mdf and original ldf safe on my external disk now as well as the orignal files on the server.
When i try to attach the database it is asking for the extra log file i created. And have tried to attach the database with removing the extra log file but it still wont attach and load the database.
I was going to use sp_attach_single_file_db but it mentions to not use it on a multi logged database.
Is there anyway i can attach this database?
For the record i have no full backup of the database!
Any help is muchly appreciated!
0
Comment
Question by:garethgrimshaw
  • 4
  • 3
8 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20026627
OK, for one the log file will grow until you do a proper backup.  Once you do a proper backup, the log file will shrink.

You can reattach a database without a log file and SQL will create one for you automatically.  Make sure the folder you reattach from doesn't have the log file in it.
0
 

Author Comment

by:garethgrimshaw
ID: 20030761
But i cannot attach it as the database now thinks there are two log files for it.
Is there a way to attach the database due to the fact i only have one of the two logs?
Thanks.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20031344
As I said if it can't find the log files it will recreate them.  Trust me I've done this MANY times.  If you've specified two log files then it will create two new blank log files.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:garethgrimshaw
ID: 20032840
Hi there,
I have tried to re-attach the database, i use Management Studio and select the re-attach option.
Point to the mdf file and the log files are listed underneath. I remove the two log files and the there are no log files in the folders specified as i have moved them elsewhere.
When i run it i get the the following error message.

Could not open new database 'SystemCenterReporting'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RepLog.ldf" may be incorrect.
File activation failure. The physical file name "E:\REPLOG2.ldf" may be incorrect.
The log was not rebuilt because there is more than one log file. (Microsoft SQL Server, Error: 1813)

I have also tried the attach_db_single_file query and get a very similar message.
Is there another sql query i can run or am i doing something wrong with the re-attachement.
Many thanks for you help so far!
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 2000 total points
ID: 20046377
Here is what I did.  I created a new database called TwoLogDB and created two logs.  I then added a single table, inserted some records, then detached the database.  I then deleted the two log files and then attempted to reattach the database and I had the same error you did!

So, before I closed the dialog box I select the down arrow by the [Script] button on the top of the dialog and selected [Script Action to New Query Window] which created the following:
USE [master]
GO
CREATE DATABASE [TwoLogDB] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TwoLogDB.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwoLogDB_Log1.ldf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwoLogDB_Log2.ldf' )
 FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'TwoLogDB' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [TwoLogDB].dbo.sp_changedbowner @loginame=N'HARRY\Ted Bouskill', @map=false
GO

I then changed it to the following and it worked!

USE [master]
GO
CREATE DATABASE [TwoLogDB] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TwoLogDB.mdf' )
 FOR ATTACH_REBUILD_LOG
GO
if exists (select name from master.sys.databases sd where name = N'TwoLogDB' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [TwoLogDB].dbo.sp_changedbowner @loginame=N'HARRY\Ted Bouskill', @map=false
GO
0
 

Author Comment

by:garethgrimshaw
ID: 20055183
Hey there,
I have just tried that script, the database is backup online!
Thanks so much for your help, we have a couple of SQL guys who didnt know about that "attach_rebuild_log" command so you have helped them as well!
Once again, thanks.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20058598
The funny thing is that in SQL 2000 you could do this with the GUI, I don't know why SQL 2005 doesn't support it.  Cheers
0
 

Expert Comment

by:rwcstaff
ID: 24936250
Congrats tedbill:  You just saved my Friday.  Thanks!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question